Search Results okc_contract_docs_details_vl




Overview
Explore in AI Chat

Overview

The OKC_CONTRACT_DOCS_DETAILS_VL view in the APPS schema is a Contracts Core (OKC) reporting object that lists contract attachments belonging to a business document type. It is a denormalized, language-aware view that joins the OKC contract documents table to the Oracle Attachment (FND) framework tables, exposing both the contract-specific attachment metadata and the descriptive attributes of the underlying attachment (category, datatype, filename or URL, and entity binding). Because it resolves the base attachment records into human-readable names using the translated (_TL) tables and applies the session language through USERENV('LANG'), the view is well suited to EBS reporting, BI Publisher data sources, and integration extracts that must present attachment details in the user's language.

The name suffix _VL indicates that the view returns language-specific descriptive columns consistent with the Multi-Language Support (MLS) conventions used throughout EBS. The view is defined in both 12.1.1 and 12.2.2, with identical structure in the documented metadata. It is a read-only view; no DML is performed against it directly.

Underlying Base Objects

The view is defined over the following documented base objects, all referenced as APPS synonyms:

  • OKC_CONTRACT_DOCS — the primary contracts table holding the attachment-to-business-document relationship, including the GENERATED_FLAG and other document control flags.
  • FND_ATTACHED_DOCUMENTS — the attachment instance table linking an entity to a document and carrying PK1–PK5 entity key values.
  • FND_DOCUMENTS — the master attachment record containing datatype, category, media reference, and file/URL information.
  • FND_DOCUMENTS_TL — the translated attachment description.
  • FND_DOCUMENT_CATEGORIES_TL — the translated category name and user name.
  • FND_DOCUMENT_DATATYPES — the datatype descriptor (e.g., file, URL) used in the DECODE that returns the URL or filename.

The join keys are ATTACHED_DOCUMENT_ID between OKC_CONTRACT_DOCS and FND_ATTACHED_DOCUMENTS, DOCUMENT_ID across the FND tables, and CATEGORY_ID and DATATYPE_ID for the reference lookups. All translated joins are constrained by LANGUAGE = USERENV('LANG').

Key Columns

  • BUSINESS_DOCUMENT_TYPE / _ID / _VERSION — identify the contract (or other business document) to which the attachment belongs, and the version at which it applies.
  • ATTACHED_DOCUMENT_ID — the attachment instance identifier, the principal join key to FND_ATTACHED_DOCUMENTS.
  • GENERATED_FLAG — indicates whether the attachment was system-generated (e.g., produced by contract generation or merge) rather than manually uploaded. This is the column most commonly filtered by users searching on "generated_flag".
  • EXTERNAL_VISIBILITY_FLAG — controls whether the attachment is visible externally, relevant to supplier/customer-facing portals.
  • INCLUDE_FOR_APPROVAL_FLAG — whether the attachment participates in the contract approval workflow.
  • PRIMARY_CONTRACT_DOC_FLAG / MERGEABLE_DOC_FLAG — identify the primary document for the contract and whether it is eligible for merge into the generated contract.
  • DELETE_FLAG — soft-delete indicator.
  • EFFECTIVE_FROM_TYPE / _ID / _VERSION — the effective-dating context of the attachment.
  • NAME / USER_NAME / DESCRIPTION — translated category name and attachment description, returned in the session language.
  • DECODE(...) expression on DATATYPE_ID — yields either the URL (datatype 5) or the file name (datatype 6).
  • DATATYPE_ID and the trailing DECODE comparing document version to effective-from version produce 'UPDATEENABLED' or 'UPDATEDISABLED', governing whether the attachment can be updated.

Common Use Cases and Queries

Typical scenarios include listing all attachments for a given contract, isolating system-generated documents, and driving version-control decisions.

List generated attachments for a contract:

SELECT ATTACHED_DOCUMENT_ID,
       NAME,
       DESCRIPTION,
       GENERATED_FLAG,
       PRIMARY_CONTRACT_DOC_FLAG
FROM   APPS.OKC_CONTRACT_DOCS_DETAILS_VL
WHERE  BUSINESS_DOCUMENT_ID = :p_contract_id
AND    GENERATED_FLAG = 'Y';

List all externally visible attachments with resolved file/URL:

SELECT ATTACHED_DOCUMENT_ID,
       DESCRIPTION,
       DECODE(DATATYPE_ID, 5, URL, 6, FILE_NAME) DOCUMENT_REF
FROM   APPS.OKC_CONTRACT_DOCS_DETAILS_VL
WHERE  BUSINESS_DOCUMENT_TYPE = 'OKC'
AND    EXTERNAL_VISIBILITY_FLAG = 'Y';

Because the view already performs the MLS lookups on descriptions and category names, it is preferable to querying the base FND and OKC tables directly when localized output is required. For high-volume extracts, restrict by BUSINESS_DOCUMENT_ID or BUSINESS_DOCUMENT_TYPE to avoid unnecessary scans of the FND attachment tables.