Results for “service_line_start_date”

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

Overview

MTL_AGREEMENT_DETAILS_V is a reporting view owned by the APPS schema and published under the INV (Inventory) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. It serves as a consolidated read-only interface that joins inventory item definitions to Oracle Contracts (OKC) agreement headers, lines, and item associations, and to client parameter records. The view is designed to answer a specific class of business question: which service or usage-billed items are attached to a given contract, and over what effective period.

The view has particular relevance for users searching on service_line_end_date. That column is derived from CONTRACT_LINES.END_DATE and represents the expiration of a contract line tied to an inventory item within a usage-based (OKX_USAGE) agreement. Because the view pivots the OKC contract hierarchy onto the item master, it allows reporting on service windows without requiring the report author to reconstruct the OKC join path manually.

Read-only access to this view is typical through BI Publisher, Oracle Discoverer, OBIEE, and custom PL/SQL packages that populate entitlement or warranty reporting. The view holds no data of its own; it is a query-time projection only.

Underlying Base Objects

The view text specifies five referenced objects:

• MTL_SYSTEM_ITEMS_B_KFV (view) — the item master key flexfield view, supplying CONCATENATED_SEGMENTS, PRIMARY_UOM_CODE, and INVENTORY_ITEM_ID. Joined on INVENTORY_ITEM_ID and ORGANIZATION_ID against the OKC item association.
• OKC_K_ITEMS (synonym) — the contract item association table. Restricts rows to JTOT_OBJECT1_CODE = 'OKX_USAGE' and links the item to a contract line via CLE_ID and to the contract via DNZ_CHR_ID.
• OKC_K_HEADERS_ALL_B (synonym) — the contract header. Supplies CONTRACT_NUMBER and the header primary key ID.
• OKC_K_LINES_B (synonym) — the contract line. Supplies SERVICE_LINE_START_DATE (START_DATE), SERVICE_LINE_END_DATE (END_DATE), and CUST_ACCT_ID.
• MTL_CLIENT_PARAMETERS (synonym) — the client parameter table, joined on CLIENT_ID = CUST_ACCT_ID to expose CLIENT_CODE and CLIENT_NUMBER.

All joins are inner joins. Any contract line lacking a matching OKX_USAGE item association, or any line whose CUST_ACCT_ID has no MTL_CLIENT_PARAMETERS row, is excluded from the result set. This behavior is important when reconciling the view against broader OKC queries.

Key Columns

• CONTRACT_NUMBER — the human-readable contract identifier from the OKC header.
• CLIENT_CODE / CLIENT_ID / CLIENT_NUMBER — the customer or client identifiers resolved through MTL_CLIENT_PARAMETERS.
• SEGMENT1 — the concatenated item flexfield segments, the practical item display value.
• PRIMARY_UOM_CODE — the item's primary unit of measure.
• SERVICE_LINE_START_DATE / SERVICE_LINE_END_DATE — the contract line effective dates; SERVICE_LINE_END_DATE is the searched attribute and governs eligibility cutoffs.
• INVENTORY_ITEM_ID — the internal item key, used for joins back to MTL_SYSTEM_ITEMS.
• CLE_ID — the contract line identifier; DNZ_CHR_ID and ID — the OKC contract header identifiers; CUST_ACCT_ID — the customer account key.

Common Use Cases and Queries

Typical scenarios include auditing service entitlements, identifying items whose service windows are about to expire, and validating that contract lines carry correct item associations.

List all active agreements with their end dates:

• SELECT contract_number, client_code, segment1, service_line_start_date, service_line_end_date FROM apps.mtl_agreement_details_v WHERE service_line_end_date >= SYSDATE ORDER BY service_line_end_date;

Extract items whose service line has already ended for a given customer:

• SELECT contract_number, segment1, service_line_end_date FROM apps.mtl_agreement_details_v WHERE client_number = :p_client AND service_line_end_date < SYSDATE;

Join back to the item master for descriptive attributes:

• SELECT a.contract_number, a.segment1, m.description, a.service_line_end_date FROM apps.mtl_agreement_details_v a, apps.mtl_system_items_b m WHERE m.inventory_item_id = a.inventory_item_id AND m.organization_id = :p_org;

Because the view is a simple projection, standard indexes on OKC_K_ITEMS, OKC_K_LINES_B, and MTL_CLIENT_PARAMETERS drive performance. Filtering on SERVICE_LINE_END_DATE is best paired with an equality or range predicate on CLIENT_ID to avoid full scans of the contract line table.