Search Results push_subq




Overview
Explore in AI Chat

Overview

APPS.AR_PAYING_RELATIONSHIPS_V is a Receivables (AR) reporting view that resolves paying relationships between parties and their related customer accounts. It exposes the payer hierarchy used by Oracle Receivables when determining which party is financially responsible for a transaction, exposing both the direct hierarchy and a "pay any" relationship through a UNION ALL of two queries. The view is owned by the APPS schema and is defined over four documented base objects: HZ_HIERARCHY_NODES, HZ_CUST_ACCOUNTS, HZ_CODE_ASSIGNMENTS, and HZ_RELATIONSHIP_TYPES (all referenced as native HZ synonyms). Its principal role is to flatten the recursive party hierarchy into a navigable two-column structure of party_id and related_party_id, allowing reports and integrations to obtain a given party's child parties and the customer accounts associated with them without traversing the hierarchy manually.

In the 12.1.1 and 12.2.2 releases, this view is used internally by Oracle Receivables for paying-relationship resolution and by customers building custom reports on top of the Trading Community Architecture (TCA) hierarchy model.

Underlying Base Objects

The view is defined over the following documented base objects:

  • HZ_HIERARCHY_NODES — the core hierarchy structure, supplying parent_id, child_id, hierarchy_type, effective_start_date, and effective_end_date. Two aliases (hn, gettop, top) are used across the UNION ALL branches to traverse the paying hierarchy.
  • HZ_CUST_ACCOUNTS — joined via hn.child_id = relacc.party_id, providing the customer account identifier (cust_account_id) and party_id for each related child party.
  • HZ_RELATIONSHIP_TYPES — supplies relationship type attributes including object_type and subject_type, restricting the hierarchy to ORGANIZATION-to-ORGANIZATION relationships.
  • HZ_CODE_ASSIGNMENTS — maps relationship types to the relationship type group PARTY_REL_GRP_AR_PAY_TOP_DOWN, enforcing that only grouped and active (status = 'A') relationship types are considered.

The EXISTS subquery joining HZ_CODE_ASSIGNMENTS and HZ_RELATIONSHIP_TYPES carries a /*+ push_subq */ hint. This instructs the optimizer to push the subquery into the surrounding query rather than evaluating it after the join, which typically reduces the number of hierarchy rows processed. That hint is the reason this view is frequently surfaced in optimizer and SQL tuning research.

Key Columns

  • party_id — the subject party (in the first branch, the hierarchy parent; in the second branch, gettop.child_id, i.e. the top parent's child).
  • related_party_id — the related party resolved via HZ_HIERARCHY_NODES to the customer account.
  • related_cust_account_id — the HZ_CUST_ACCOUNTS identifier belonging to the related party.
  • hierarchy_type — the hierarchy type from HZ_HIERARCHY_NODES, also matched to rt.relationship_type in the EXISTS clause.
  • relationship_type_group_name — either 'PARTY_REL_GRP_AR_PAY_TOP_DOWN' (direct hierarchy branch) or 'PARTY_REL_GRP_AR_PAY_ANY' (pay-any branch).
  • effective_start_date / effective_end_date — the validity window. In the UNION ALL branch these are computed using GREATEST across effective_start_date values and LEAST across effective_end_date values of the contributing hierarchy rows.

Common Use Cases and Queries

This view is most often queried to derive the set of party/account pairs that a payer hierarchy produces for a given party, for use in payment application, statement generation, or custom AR reporting.

SELECT party_id, related_party_id, related_cust_account_id,
       hierarchy_type, relationship_type_group_name,
       effective_start_date, effective_end_date
FROM   apps.ar_paying_relationships_v
WHERE  party_id = :p_party_id;

To isolate only the direct (top-down) relationship group:

SELECT * FROM apps.ar_paying_relationships_v
WHERE relationship_type_group_name = 'PARTY_REL_GRP_AR_PAY_TOP_DOWN';

To resolve a related party to its customer account and filter by current effectiveness:

SELECT p.party_id, p.related_party_id, p.related_cust_account_id
FROM   apps.ar_paying_relationships_v p
WHERE  p.related_cust_account_id = :p_cust_account_id
AND    TRUNC(SYSDATE) BETWEEN p.effective_start_date AND NVL(p.effective_end_date, TRUNC(SYSDATE));

Because the definition contains a /*+ push_subq */ hint, tuning efforts on this view should verify whether the hint is still beneficial on the target database version; in some 12.2.2 environments it can be overridden by SQL profiles or removed via a patched version of the view. Querying the view directly is preferable to reimplementing the hierarchy join, since it is maintained with the standard Receivables paying-relationship semantics.