Search Results sys_link_end_date_active




Overview
Explore in AI Chat

Overview

APPS.POR_EXPENDITURE_LOV_V is a reporting and list-of-values (LOV) view in Oracle E-Business Suite 12.1.1 and 12.2.2. It exposes a filtered set of expenditure types that are valid for use in a specific system linkage context — the 'VI' system linkage function — and that are currently active based on date-range checks. Its name (POR prefix, _LOV_V suffix) indicates its primary role: populating selection lists in Purchasing/Oracle Projects forms where users must choose an expenditure type. Because it applies both expenditure-type date validation and system-linkage date validation against SYSDATE, the view returns only rows usable "today," which makes it well suited to runtime LOV and reporting rather than historical analysis.

Underlying Base Objects

The view is defined over a single documented base object, PA_EXPENDITURE_TYPES_EXPEND_V, which is itself a view in the Projects (PA) module. The POR_EXPENDITURE_LOV_V definition selects from that view (aliased ET) and applies three predicates:

  • ET.system_linkage_function = 'VI' — restricts rows to expenditure types linked to the 'VI' system linkage function.
  • TRUNC(SYSDATE) BETWEEN ET.expnd_typ_start_date_active AND NVL(ET.expnd_typ_end_date_active, TRUNC(SYSDATE+1)) — enforces current validity of the expenditure type itself.
  • TRUNC(SYSDATE) BETWEEN ET.sys_link_start_date_active AND NVL(ET.sys_link_end_date_active, TRUNC(SYSDATE+1)) — enforces current validity of the system linkage, using the SYS_LINK_START_DATE_ACTIVE and SYS_LINK_END_DATE_ACTIVE columns.

The term the user searched, sys_link_end_date_active, is central to the definition: it bounds the upper end of the system-linkage effective period and is wrapped in NVL(..., TRUNC(SYSDATE+1)) so that open-ended linkages (no end date) are treated as active through the current date.

Key Columns

  • EXPENDITURE_TYPE — the expenditure type identifier/name presented to the user in the LOV.
  • DESCRIPTION — the descriptive text for the expenditure type.
  • SYS_LINK_START_DATE_ACTIVE — start date of the system linkage's active period.
  • SYS_LINK_END_DATE_ACTIVE — end date of the system linkage's active period; NULL implies open-ended, treated as active through today by the NVL logic.

Columns such as EXPND_TYP_START_DATE_ACTIVE and EXPND_TYP_END_DATE_ACTIVE are consumed from the base view in the WHERE clause but are not projected in the outer SELECT list.

Common Use Cases and Queries

Typical scenarios include populating expenditure type LOVs on transactional forms and validating that a chosen expenditure type is currently linked and active before processing. A basic query retrieving all currently valid, system-linked expenditure types is:

SELECT expenditure_type, description,
       sys_link_start_date_active, sys_link_end_date_active
FROM   apps.por_expenditure_lov_v
ORDER  BY expenditure_type;

To confirm whether a specific expenditure type is currently available, filter on the name:

SELECT expenditure_type, description
FROM   apps.por_expenditure_lov_v
WHERE  expenditure_type = :p_expenditure_type;

Because the view hard-codes SYSDATE filtering, it cannot be used directly for historical reporting; to inspect underlying linkages across time, query PA_EXPENDITURE_TYPES_EXPEND_V directly and apply explicit date ranges against SYS_LINK_START_DATE_ACTIVE and SYS_LINK_END_DATE_ACTIVE. When troubleshooting missing LOV entries, verify both the expenditure type's own active dates and its system-linkage dates, since a missing or expired SYS_LINK_END_DATE_ACTIVE can exclude otherwise valid rows.