Search Results ptp_party

Overview

ZX_EXEMPTIONS_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, classified under the FND – Application Object Library product group. It is part of the E-Business Tax (ZX) module infrastructure and exposes exemption certificate data in a denormalized form suitable for inquiry, reporting, and integration consumption. The view consolidates rows from the base transaction table ZX_EXEMPTIONS and joins them to lookup, party tax profile, and operating unit entities to present human-readable exemption information.

The view is defined over the synonyms and views ZX_EXEMPTIONS, ZX_PARTY_TAX_PROFILE, HZ_PARTY_SITES, FND_LOOKUP_VALUES, HR_OPERATING_UNITS, GL_LEDGER_LE_V, and XLE_ENTITY_PROFILES. It is a UNION ALL construct: the first branch resolves third-party exemption certificates registered against a party tax profile, while the second branch resolves certificates tied to a specific party site. Both branches normalize the exemption data into a common column list.

Underlying Base Objects

The primary driver is ZX_EXEMPTIONS, which stores exemption certificate records including certificate number, reason code, status, rate modifier, tax regime, effective dates, customer account, site use, and the content owner (the operating unit or legal entity that owns the record). FND_LOOKUP_VALUES supplies the translated meaning for the EXEMPT_REASON_CODE lookup type 'ZX_EXEMPTION_REASON_CODE', filtered by security group 0 and the session language. ZX_PARTY_TAX_PROFILE is joined twice in the first branch — once as PTP_PARTY for the third-party tax profile and once as OU for the content owner profile. HZ_PARTY_SITES and ZX_PARTY_TAX_PROFILE are used in the second branch to resolve the party site. HR_OPERATING_UNITS, GL_LEDGER_LE_V, and XLE_ENTITY_PROFILES support the operating unit and legal entity context that the products layer relies on when filtering by legal entity.

Key Columns

• EXEMPT_CERTIFICATE_NUMBER — the certificate identifier; rows with NULL are excluded by the view definition.
• EXEMPT_REASON_CODE / MEANING — the coded reason and its translated display meaning.
• EXEMPTION_STATUS_CODE — restricted to PRIMARY, MANUAL, or UNAPPROVED; UNAPPROVED and MANUAL certificates are surfaced alongside fully approved ones.
• RATE_MODIFIER, TAX_REGIME_CODE — the tax treatment applied when the exemption is honored.
• CUST_ACCOUNT_ID, SITE_USE_ID — the customer and site use the certificate applies to.
• PARTY_ID, PARTY_SITE_ID — the resolved party in the first branch; the second branch supplies the site-level party in PARTY_SITE_ID.
• CONTENT_OWNER_ID, ORG_ID — the owning profile and the operating unit party identifier.
• LE_ID — explicitly selected as NULL in both branches. The view text contains a comment noting that products should join using NVL(ZX_EXEMPTIONS_V.LE_ID, :LE_ID) = :LE_ID, with :LE_ID supplied by the calling product. This is the column relevant to the search term "le_id": it is intentionally NULL at the view level and is populated by the consuming query, allowing legal-entity-scoped filtering without hard-coding the entity in the view.
• EFFECTIVE_FROM / EFFECTIVE_TO — the validity window of the certificate.

Common Use Cases and Queries

The view supports exemption certificate reporting, tax determination debugging, and integration extracts where exemption data must be presented with descriptive reasons and owner context. A typical query joining for a legal entity is:

SELECT exempt_certificate_number,
       exempt_reason_code,
       meaning,
       exemption_status_code,
       party_id,
       org_id,
       effective_from,
       effective_to
FROM   zx_exemptions_v
WHERE  NVL(le_id, :le_id) = :le_id
AND    TRUNC(SYSDATE) BETWEEN effective_from AND NVL(effective_to, TRUNC(SYSDATE));

The NVL pattern is significant: because LE_ID is returned as NULL, the predicate resolves to :le_id = :le_id when the view row is not entity-specific, and filters correctly when the products layer supplies an entity-specific value. Analysts querying the view directly should therefore pass a bind variable rather than filtering on LE_ID alone, since a literal comparison against NULL would return no rows. Additional common filters include CUST_ACCOUNT_ID for customer-level analysis, ORG_ID for operating unit reporting, and EXEMPTION_STATUS_CODE to isolate unapproved or manual certificates requiring review.