Results for “pn_var_lines_dates_v”

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

Overview

PN_VAR_LINES_DATES_V is a form view owned by the APPS schema in Oracle E-Business Suite, registered under the PN – Property Manager product. Its documented purpose is to present variable rent line item details — specifically the intersection of variable rent periods, their associated line items, and the frequency and lookup attributes that describe how each line is invoiced, reconciled, reported, and audited. In Release 12.1.1 and 12.2.2 the view is deployed in a VALID state and is used primarily as the query surface behind Property Manager variable rent forms, giving the form layer a single denormalized row per period/line combination without requiring the form to join the four underlying PN tables itself.

Because the view resolves all code values through FND_LOOKUPS and exposes both the raw code and its translated meaning, it is equally suitable for reporting and integration: concurrent programs, Oracle Reports, BI Publisher data templates, and inbound/outbound interfaces can consume it to obtain human-readable sales channel and item category descriptions alongside their coded identifiers.

Underlying Base Objects

The documented base objects behind PN_VAR_LINES_DATES_V are PN_VAR_PERIODS_ALL, PN_VAR_LINES_ALL, PN_VAR_RENTS, PN_VAR_RENT_DATES_ALL, and FND_LOOKUPS. PN_VAR_RENTS is the parent variable rent record; PN_VAR_RENT_DATES_ALL holds its frequency definitions (invoice, reconciliation, reporting, and audit); PN_VAR_PERIODS_ALL holds the individual periods belonging to that rent; and PN_VAR_LINES_ALL holds the line items booked within each period.

The join path is: PN_VAR_LINES_ALL.PERIOD_ID = PN_VAR_PERIODS_ALL.PERIOD_ID, PN_VAR_PERIODS_ALL.VAR_RENT_ID = PN_VAR_RENTS.VAR_RENT_ID, and PN_VAR_RENTS.VAR_RENT_ID = PN_VAR_RENT_DATES_ALL.VAR_RENT_ID. Two outer joins to FND_LOOKUPS resolve the descriptive meanings — one against lookup type PN_SALES_CHANNEL for LINES.SALES_TYPE_CODE, and one against lookup type PN_ITEM_CATEGORY for LINES.ITEM_CATEGORY_CODE. The outer joins ensure that a line item is still returned when the corresponding lookup code is not defined or has been end-dated, which is the standard defensive pattern in ETRM views. This is the mechanism that answers a search for "pn_item_category": the item category lookup is not stored as a base-table attribute of this view but is resolved at query time from FND_LOOKUPS using lookup type PN_ITEM_CATEGORY.

Key Columns

• PERIOD_START_DATE / PERIOD_END_DATE / PERIOD_NUM – The date range and sequence number of the variable rent period, selected from PN_VAR_PERIODS_ALL.
• SALES_CHANNEL – The translated meaning of LINES.SALES_TYPE_CODE from lookup type PN_SALES_CHANNEL.
• ITEM_CATEGORY – The translated meaning of LINES.ITEM_CATEGORY_CODE from lookup type PN_ITEM_CATEGORY.
• SALES_TYPE_CODE / ITEM_CATEGORY_CODE – The underlying coded values used for filtering, grouping, and interface mapping.
• INVG_FREQ_CODE, RECON_FREQ_CODE, REPTG_FREQ_CODE, AUDIT_FREQ_CODE – Frequency codes governing invoicing, reconciliation, reporting, and auditing for the variable rent.
• UOM, RENT_NUM – The unit of measure and variable rent number, providing identification and context for the parent rent.
• LINE_ITEM_NUM – The line item sequence within the period.
• ROW_ID, LINE_ITEM_ID, PERIOD_ID – The ROWID of PN_VAR_LINES_ALL, the line item identifier, and the owning period identifier; these support form navigation and DML against the base line records.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN – Standard EBS audit columns carried through from PN_VAR_LINES_ALL.
• ORG_ID – The operating unit, from PN_VAR_RENTS, enabling Multi-Org (VPD) filtering.

Common Use Cases and Queries

Typical uses include reviewing variable rent billing detail by period, validating that item category and sales channel codes are mapped to active lookup values, and feeding downstream revenue or lease-analysis extracts. Because the view carries ORG_ID from the parent rent and LINE_ITEM_ID from the line, it is also convenient for reconciliation between periods.

To list period-level variable rent detail for a specific item category:

• SELECT period_num, period_start_date, period_end_date, rent_num, line_item_num, sales_channel, item_category FROM apps.pn_var_lines_dates_v WHERE org_id = :p_org_id AND item_category_code = 'LEASE' ORDER BY rent_num, period_num, line_item_num;

To identify line items whose item category code is unresolved in FND_LOOKUPS, the outer join behavior can be tested by comparing the raw code with the translated value:

• SELECT line_item_id, period_id, item_category_code, item_category FROM apps.pn_var_lines_dates_v WHERE item_category_code IS NOT NULL AND item_category IS NULL;

To summarize sales channel totals by period for reporting purposes:

• SELECT period_num, sales_channel, item_category, COUNT(*) line_count FROM apps.pn_var_lines_dates_v WHERE org_id = :p_org_id GROUP BY period_num, sales_channel, item_category ORDER BY period_num;