Results for “inv_3pl_contracts_v”
12 results
AI-generated from documented ETRM metadata — verify critical details on the linked pages.
Overview
INV_3PL_CONTRACTS_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the Inventory (INV) product family. It is registered as VALID and is available in both 12.1.1 and 12.2.2 release levels. The view exposes a consolidated, denormalised representation of third-party logistics (3PL) service contracts, joining Oracle Contracts Core (OKC) contract headers to billing rule definitions, trading partner identities, and the operating unit that authored the agreement.
Rather than presenting raw foreign keys alone, the view synthesises a human-readable contract label by concatenating the contract number, the object version number, the party name of the counterparty, and the operating unit name, delimited by hyphens. This design makes the view particularly suitable for direct use in concurrent program reports, Oracle Business Intelligence Publisher data templates, and ad hoc SQL where the analyst requires a single-column contract descriptor without performing multi-table joins manually. Because it is a view over other views and synonyms rather than a base table, it carries no storage of its own and inherits the security behaviour of its underlying objects, including operating unit filtering through HR_SECURITY and HR_GENERAL.
Underlying Base Objects
The view is defined over the following documented objects:
- MTL_SERVICE_CONTRACTS_V — the driving contract source, supplying CONTRACT_NUMBER, ID, and AUTHORING_ORG_ID.
- MTL_BILLING_RULE_HEADERS_VL — joined on SERVICE_AGREEMENT_ID = CONTRACTS.ID, supplying BILLING_RULE_HEADER_ID and the rule NAME.
- OKC_K_PARTY_ROLES_B — the Contracts Core party-role table (referenced via synonym), linked by CHR_ID to the contract and by OBJECT1_ID1 to the party identifier.
- HZ_PARTIES — the Trading Community Architecture party master, joined via synonym to resolve PARTY_NAME.
- HR_OPERATING_UNITS — supplies the operating unit NAME corresponding to AUTHORING_ORG_ID.
- HR_GENERAL and HR_SECURITY — packages referenced indirectly that enforce organisational access and operating unit restrictions.
The join between OKC_K_PARTY_ROLES_B and the contract is of particular note: CONT_PARTY.CHR_ID equates to CONTRACTS.ID, and the party is resolved through OBJECT1_ID1. This means the party role rows must be correctly configured for the contract to appear, and a contract lacking a valid party role will be excluded from the result set.
Key Columns
- CONTRACT — a concatenated display string combining contract number, object version number, party name, and operating unit name. Intended for presentation, not for joins or filters.
- AUTHORING_ORG_ID — the ORGANIZATION_ID of the operating unit that authored the contract. Useful for operating unit level reporting and for joining to HR_ALL_ORGANIZATION_UNITS.
- BILLING_RULE_HEADER_ID — identifier of the billing rule header associated with the contract, used to join to billing rule detail structures.
- RULE_NAME — the descriptive name of the billing rule, useful for grouping and reporting on billing arrangements.
- CONTRACT_NUMBER — the business-facing contract number, suitable for user-supplied search parameters.
- ID — the internal contract identifier corresponding to MTL_SERVICE_CONTRACTS_V.ID and OKC contract CHR_ID, suitable as a join key to related contract tables.
Common Use Cases and Queries
The principal use case is enumerating 3PL service contracts with their billing rules for a given operating unit or trading partner. Because the view is defined against operating-unit-secured objects, results are naturally filtered by the responsibility's organisation access.
A typical listing query is:
- SELECT contract, contract_number, rule_name, authoring_org_id FROM apps.inv_3pl_contracts_v WHERE authoring_org_id = :p_org_id ORDER BY contract_number;
To retrieve billing rules for a single known contract:
- SELECT contract,billing_rule_header_id,rule_name FROM apps.inv_3pl_contracts_v WHERE contract_number = :p_contract_number;
To count billing rules per contract for capacity or audit reporting:
- SELECT contract_number, COUNT(billing_rule_header_id) rule_count FROM apps.inv_3pl_contracts_v GROUP BY contract_number ORDER BY rule_count DESC;
Because CONTRACT is a concatenated presentation column, joins and filters should always target ID or CONTRACT_NUMBER instead. Performance is generally acceptable for reporting volumes; where larger extracts are required, materialising the result into a staging table is recommended to avoid repeated resolution of the underlying contract and party views.
-
View: INV_3PL_CONTRACTS_V 12.2.2
APPS.INV_3PL_CONTRACTS_V·↳ HR_GENERAL·↳ HR_OPERATING_UNITS·↳ HR_SECURITY·Explore INV module →
-
12.2.2 DBA Data 12.2.2
-
12.2.2 FND Design Data 12.2.2
-
SYNONYM: APPS.HZ_PARTIES 12.2.2
-
eTRM - INV Tables and Views 12.2.2
-
12.2.2 DBA Data 12.2.2
-
eTRM - INV Tables and Views 12.2.2