Results for “sy_docs_mst”

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

Overview

SY_DOCS_MST is the Oracle Process Manufacturing (OPM) Document Master table, owned by the GMA schema within Oracle EBS 12.1.1 and 12.2.2. It stores the master definitions of documents used throughout the Process Manufacturing and Inventory modules, providing a central registry of document types (DOC_TYPE) that govern transactional, costing, and inventory event records. The table acts as a reference and control-point repository: downstream operational tables in purchasing, inventory, and process execution carry a DOC_TYPE foreign key that resolves to this master to obtain document descriptions, control flags, and text associations. Because it supplies the descriptive and semantic context for the many fact tables that point to it, and because it holds relatively stable reference data rather than high-volume transactional rows, the metadata's heuristic Data Vault classification of hub-leaning is consistent with its role as a reference hub. Modelers may treat SY_DOCS_MST as a hub keyed by DOC_TYPE, with descriptive attributes handled as satellite data.

Key Information Stored

The table contains twelve documented columns, of which the most significant are:

• DOC_TYPE — The document type identifier and the column that forms the primary key SY_DOCS_MST_PK. It is the business key referenced by all dependent tables and is the single most important column for joins.
• DOC_DESC — The description of the document type, used for display and reporting wherever a DOC_TYPE appears.
• DESC_ENGLISH — An English-language description variant supporting multilingual or translation scenarios.
• TEXT_CODE — A foreign key to SY_TEXT_HDR, linking the document definition to a text header; this permits long-form text or notes to be attached to a document type.
• TRANS_CNT — A transaction counter associated with the document type, useful for tracking activity volume.
• DELETE_MARK — The standard Oracle soft-delete flag; rows with a non-zero value are logically deleted and should be excluded from active reference lookups.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard audit columns recording insert and update provenance, essential for change tracking and data governance.
• ZD_EDITION_NAME — The editioning column supporting Oracle EBS 12.2 online patching; it participates in the unique index alongside DOC_TYPE.

The unique index SY_DOCS_MST_PK is defined on (DOC_TYPE, ZD_EDITION_NAME), establishing DOC_TYPE as the business-key candidate. No separate meaningless surrogate key is documented; DOC_TYPE itself serves as the effective primary identifier.

Common Use Cases and Queries

A frequent requirement, reflected in searches for "po_cost_dtl," is tracing purchasing cost and distribution records back to their document type definition. Analysts join PO_COST_DTL.DOC_TYPE to SY_DOCS_MST.DOC_TYPE to attach human-readable descriptions to cost lines. A representative query is:

SELECT p.cost_id, p.doc_type, m.doc_desc FROM po_cost_dtl p JOIN sy_docs_mst m ON p.doc_type = m.doc_type WHERE m.delete_mark = 0;

Additional practical patterns include:

• Populating LOVs and value sets for DOC_TYPE fields across OPM and Inventory forms.
• Reporting transaction volumes by joining TRANS_CNT or aggregating dependent transaction tables (IC_TRAN_PND, IC_TRAN_ARC) grouped by DOC_TYPE.
• Retrieving attached long text by joining TEXT_CODE to SY_TEXT_HDR for document-level notes.
• Data-quality checks that identify orphaned DOC_TYPE values in dependent tables that do not resolve to this master.

Related Objects

SY_DOCS_MST participates in an extensive foreign-key web. It references SY_TEXT_HDR via TEXT_CODE. Numerous tables reference it through DOC_TYPE, including PO_COST_DTL, PO_DIST_DTL, IC_ADJS_JNL (via TRANS_TYPE), IC_CYCL_ADT, IC_TRAN_ARC, IC_TRAN_CMP, IC_TRAN_PND, MR_ACTN_TBL, PC_TRAN_PND, and SY_DOCS_SEQ. The SY_DOCS_SEQ relationship is particularly notable, as it typically supplies document numbering sequences per DOC_TYPE, allowing transaction numbering to be controlled centrally from the same master definition. These dependencies make SY_DOCS_MST a pivotal reference table for both inventory and process manufacturing transaction processing in Oracle EBS.