Search Results pn_var_deductions




Overview
Explore in AI Chat

Overview

APPS.PN_VAR_DEDUCTIONS_V is a reporting and inquiry view within the Oracle E-Business Suite Property Manager (PN) module, which supports the administration of leased and owned property portfolios. The view exposes deduction records associated with variable-rate (participating) lease calculations, presenting them in a denormalized, human-readable form suitable for concurrent programs, Discoverer workbooks, and BI Publisher reports. It is defined in the APPS schema and is intended for read-only consumption rather than transactional maintenance; inserts, updates, and deletes against deduction data must target the underlying PN_VAR_DEDUCTIONS base table through the corresponding Property Manager forms and APIs.

The view joins lookup-driven decoding and hierarchical calculation logic so that consumers receive deduction amounts already translated into descriptive lookup meanings and supplemented with a running cumulative amount per line item. This makes it a convenient single source for reconciliation, audit, and interface extracts in both release 12.1.1 and 12.2.2, where the object definition remains materially consistent.

Underlying Base Objects

The view is defined over four primary data sources, each referenced through an APPS synonym or view, plus one package dependency:

  • PN_VAR_DEDUCTIONS (SYNONYM, aliased ded) — the driving base table containing one row per deduction, keyed by DEDUCTION_ID and linked by LINE_ITEM_ID and PERIOD_ID.
  • PN_VAR_LINES_ALL (SYNONYM, aliased lines) — the variable-rate line items to which deductions belong; the join is lines.line_item_id = ded.line_item_id and enforces the multi-org (ORG_ID) context.
  • PN_VAR_PERIODS_ALL (SYNONYM, aliased per) — the variable-rate accounting periods; the join per.period_id = ded.period_id links each deduction to its period.
  • FND_LOOKUPS (VIEW, aliased fnd) — supplies the decoded DEDUCTION_TYPE value from lookup type PN_DEDUCTION_TYPE. This join is an outer join (denoted by (+)), so deductions with an undefined or inactive lookup code are still returned.
  • FND_GLOBAL (PACKAGE) — referenced for runtime session context (for example, ORG_ID and user identity), consistent with the standard EBS 12.1.1/12.2.2 multi-org architecture.

Key Columns

Identifier and audit columns include DEDUCTION_ID and DEDUCTION_NUM (the business-facing deduction number), plus the standard WHO columns LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN. DEDUCTION_ID is also exposed as ROW_ID via ded.ROWID, supporting tools that require a row identifier.

Organizational and temporal columns include ORG_ID, LINE_ITEM_ID, and PERIOD_ID, together with START_DATE, END_DATE, GROUP_DATE, INVOICING_DATE, and GRP_DATE_ID, which drive the timing of variable-rate deduction processing. GL_ACCOUNT_ID identifies the distribution account.

Financial and categorical columns include DEDUCTION_TYPE_CODE and its decoded counterpart DEDUCTION_TYPE (from FND_LOOKUPS), DEDUCTION_AMOUNT, a computed deduction_cum_amt derived using a windowed SUM partitioned by LINE_ITEM_ID and ordered by DEDUCTION_NUM, and EXPORTED_CODE, which indicates whether the deduction has been exported to another system. COMMENTS holds free-text notes, and ATTRIBUTE_CATEGORY plus ATTRIBUTE1 through ATTRIBUTE15 provide the standard flexfield descriptor columns. All base-table columns are passed through unchanged from PN_VAR_DEDUCTIONS, so the view adds decoding and the cumulative measure rather than altering stored values.

Common Use Cases and Queries

Typical scenarios include reconciling deductions against variable-rate line items, producing period close reports, and generating interface extracts for downstream financial systems. A basic inquiry returns deductions for a given period:

SELECT deduction_num, deduction_type, deduction_amount,
       deduction_cum_amt, start_date, end_date
FROM   apps.pn_var_deductions_v
WHERE  period_id = :p_period_id
ORDER  BY line_item_id, deduction_num;

To report deductions by decoded type for a line item, filtering on the lookup meaning avoids dependencies on the code values:

SELECT deduction_num, deduction_type, deduction_amount, exported_code
FROM   apps.pn_var_deductions_v
WHERE  line_item_id = :p_line_item_id
AND    deduction_type_code = :p_type_code;

Exported and unexported totals are frequently summarized for interface control:

SELECT exported_code, COUNT(*) cnt, SUM(deduction_amount) total_amt
FROM   apps.pn_var_deductions_v
WHERE  org_id = :p_org_id
GROUP  BY exported_code;

Note that the cumulative amount resets per LINE_ITEM_ID and relies on DEDUCTION_NUM ordering; consumers requiring period-level accumulation must aggregate explicitly. As with all multi-org views in EBS, queries should be executed with the correct operating unit context to ensure ORG_ID filtering behaves as expected.