Results for “payment_item_id”

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

Overview

PN_TRX_LEASE_REVENUE is a reporting view owned by the APPS schema within the Oracle Property Manager (PN) module. It consolidates lease-level payment information with customer, site, location, and lookup-derived attributes, presenting a denormalized result set suited to inquiry, reporting, and integration. The view is documented as VALID in ETRM for release 12.2.2 and is equally applicable to 12.1.1, where the same Property Manager payment architecture is present.

The central design intent of the view is the isolation of normalized lease payment items. A filter on ITEM.PAYMENT_ITEM_TYPE_LOOKUP_CODE = 'NORMALIZED' restricts output to normalized payment items, and a filter on LEASE.LEASE_CLASS_CODE <> 'DIRECT' excludes direct-class leases. The result is a projection oriented toward operational lease revenue terms rather than direct billing arrangements. Because the underlying objects include multi-org and TCA customer entities, the view itself carries no organization security enforcement; callers are expected to constrain results through the base tables or standard access controls when required.

Underlying Base Objects

The view is defined over the following documented objects: PN_PAYMENT_ITEMS (synonym), PN_PAYMENT_TERMS_ALL (synonym), PN_LEASES_ALL (synonym), PN_LOCATIONS_ALL (synonym), FND_LOOKUPS (view), FND_GLOBAL (package), and the Oracle Trading Community Architecture entities HZ_PARTIES, HZ_CUST_ACCOUNTS_ALL, and HZ_CUST_SITE_USES_ALL (all synonyms).

Joins are driven from PN_PAYMENT_ITEMS to PN_PAYMENT_TERMS_ALL on PAYMENT_TERM_ID, then to PN_LEASES_ALL on LEASE_ID. PN_LOCATIONS_ALL is joined as an outer join on LOCATION_ID, so payment terms without an assigned location are retained with a null LOCATION_CODE. Customer identification resolves through TERM.CUSTOMER_ID to HZ_CUST_ACCOUNTS_ALL and onward to HZ_PARTIES, while the site use is resolved through TERM.CUSTOMER_SITE_USE_ID to HZ_CUST_SITE_USES_ALL. Two instances of FND_LOOKUPS supply decoded meanings: PN_PAYMENT_TERM_TYPE for the payment type and PN_PAYMENT_PURPOSE_TYPE for the payment purpose. FND_GLOBAL is referenced as a supporting package, typically for session context such as user and responsibility identifiers.

Key Columns

• PAYMENT_ITEM_ID — Primary identifier of the payment item from PN_PAYMENT_ITEMS; the natural key for row-level joins.
• LEASE_NUMBER — Lease number from PN_LEASES_ALL, the human-readable lease identifier.
• LEASE_NAME — Descriptive name of the lease.
• CUSTOMER_NAME — Party name of the customer from HZ_PARTIES.
• CUSTOMER_SITE — Site use location identifier for the customer billing or remit site.
• PAYMENT_PURPOSE — Decoded meaning of the payment purpose code via the PN_PAYMENT_PURPOSE_TYPE lookup.
• PAYMENT_TYPE — Decoded meaning of the payment term type code via the PN_PAYMENT_TERM_TYPE lookup.
• LOCATION_CODE — Location code from PN_LOCATIONS_ALL, nullable because of the outer join.

Common Use Cases and Queries

Typical uses include lease revenue reporting, reconciliation of normalized payment items to customer accounts, and extracts feeding downstream billing or analytics. A representative query listing normalized lease revenue rows for a single lease follows:

SELECT payment_item_id, lease_number, lease_name, customer_name, customer_site, payment_purpose, payment_type, location_code
FROM apps.pn_trx_lease_revenue
WHERE lease_number = :p_lease_number
ORDER BY payment_item_id;

For customer-centric reporting, aggregate by customer and purpose:

SELECT customer_name, payment_purpose, payment_type, COUNT(*) item_count
FROM apps.pn_trx_lease_revenue
GROUP BY customer_name, payment_purpose, payment_type
ORDER BY customer_name;

Because LOCATION_CODE may be null, queries that require an assigned location should add WHERE location_code IS NOT NULL. Joins to additional PN or TCA tables are best driven from PAYMENT_ITEM_ID or LEASE_NUMBER, and performance is generally improved by filtering on lease attributes before aggregation.