Search Results contract_line




Overview
Explore in AI Chat

Overview

APPS.INV_3PL_CLIENTS_V is a reporting view in Oracle E-Business Suite that exposes third-party logistics (3PL) client information in relation to active service contracts. It is defined as a DISTINCT select joining client parameter records against service contract headers, filtered through a correlated EXISTS subquery over billing rule lines, billing rule headers, and contract lines. The view answers the question: which 3PL clients currently have an effective service agreement line whose billing rules match the client code?

In the ETRM 12.2.2 documentation, the view is listed with owner APPS and is consumed primarily for reporting and integration scenarios where downstream processes require a list of valid, contract-backed client accounts. Because the EXISTS clause constrains results by SYSDATE BETWEEN contract_line.start_date AND contract_line.end_date, the view is inherently date-sensitive: only clients with a currently valid contract line are returned at query time.

Underlying Base Objects

The documented base objects are HR_GENERAL (package), HR_SECURITY (package), MTL_BILLING_RULE_HEADERS_VL (view), MTL_BILLING_RULE_LINES (synonym), MTL_CLIENT_PARAMETERS_V (view), MTL_SERVICE_CONTRACTS_V (view), and OKC_K_LINES_B (synonym). The HR packages are referenced indirectly to enforce organization-level security via authoring_org_id. The remaining objects form the functional join chain:

  • MTL_CLIENT_PARAMETERS_V — supplies the client identity attributes (name, code, number, id).
  • MTL_SERVICE_CONTRACTS_V — supplies the contract header and the authoring organization.
  • MTL_BILLING_RULE_HEADERS_VL and MTL_BILLING_RULE_LINES — link the contract to the billing rule logic and to the client code.
  • OKC_K_LINES_B — provides the contract line (service agreement line) that determines date validity.

The EXISTS subquery correlates client_param.client_code = rule_line.client_code, joins the rule line to the contract line and the rule header, and ties the rule header back to the contract header, giving the view its contract-scoped, date-filtered semantics.

Key Columns

The view exposes five columns, all sourced from the outer query:

  • CLIENT_NAME — the descriptive name of the 3PL client, from MTL_CLIENT_PARAMETERS_V.
  • CLIENT_CODE — the business identifier used to join the client to billing rule lines; central to the EXISTS correlation.
  • CLIENT_NUMBER — the numeric or alternate identifier for the client.
  • CLIENT_ID — the surrogate primary key of the client record, useful for downstream joins.
  • AUTHORING_ORG_ID — the organization that authored the associated service contract, from MTL_SERVICE_CONTRACTS_V; used with HR_SECURITY for row-level access control.

The DISTINCT keyword ensures a client appears once even when multiple matching contract lines or billing rules exist.

Common Use Cases and Queries

A frequent scenario is validating which clients are currently under contract before creating billing or shipment transactions. Because the query term "contract_line" appears in the view definition, the object is often located during troubleshooting of contract-line-driven client lists. A representative query follows:

  • SELECT client_name, client_code, client_number, authoring_org_id FROM apps.inv_3pl_clients_v WHERE client_code = :p_client_code;
  • SELECT client_id, client_name FROM apps.inv_3pl_clients_v ORDER BY client_name;
  • Joining the view back to OKC_K_LINES_B on the client-linked contract to report contract line start and end dates for each active client.

Typical uses include 3PL self-service pages, client lookup lists of values, and integration extracts that must exclude clients lacking an effective billing-covered contract. Because the view is date-driven, results change over time without any data modification, which must be considered when the output is cached or interfaced to external systems.