Search Results dqend_date




Overview
Explore in AI Chat

Overview

HZ_DQM_PS_U_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the Receivables (AR) product family. Its name indicates a "Data Quality Management" (DQM) presentation view built over party site use data. The view exposes a denormalized, filter-ready projection of the HZ_PARTY_SITE_USES entity, augmented with lookup-derived meaning columns, so that DQM validation, duplicate-checking, and data-cleansing routines can evaluate party site use records without embedding lookup joins or status filtering logic directly in application code.

The view identifies itself as a presentation-layer object: every functional column carries the "DQ" prefix (for example, DQPARTY_SITE_USE_ID, DQSTATUS), which distinguishes it from the base entity columns and signals that the output is intended for comparison or matching routines rather than transactional maintenance. One business rule is hard-coded into the view definition: records whose STATUS is 'M' are excluded. Combined with the NVL default of 'A', the view returns only rows that are either explicitly active or have no status at all, thereby removing merged or logically deleted party site uses from downstream evaluation. The view is classified with a VALID status in ETRM 12.2.2 and is also present in 12.1.1.

Underlying Base Objects

The view definition references three documented base objects:

  • HZ_PARTY_SITE_USES (SYNONYM) — the primary driving entity, aliased as A in the view text. It supplies the party site use identifier, effective dates, comments, site use type, primary-per-type flag, and status. Through the APPS synonym, this resolves to the Trading Community Architecture (TCA) table that stores the role a party site plays for a party, such as bill-to, ship-to, or statement-to.
  • AR_LOOKUPS (VIEW) — joined with an outer join on LOOKUP_TYPE = 'PARTY_SITE_USE_CODE' to translate SITE_USE_TYPE into a human-readable MEANING. Because the join is outer (+), a site use row is not dropped when no matching lookup code exists.
  • ARPT_SQL_FUNC_UTIL (PACKAGE) — a Receivables utility package that exposes GET_LOOKUP_MEANING. The view calls this function for the 'REGISTRY_STATUS' lookup to produce DQSTATUSM, a translated status description; if the lookup returns nothing, the raw status value is returned via NVL.

The relationship is therefore a two-level enrichment: base party site use rows are filtered, then decorated with two independent lookup translations.

Key Columns

  • DQPARTY_SITE_USE_ID — unique identifier for the party site use record; the primary key of the underlying entity.
  • DQPARTY_SITE_ID — foreign key to the associated party site, linking the use to its location.
  • DQSITE_USE_TYPE — code indicating the role of the site (for example bill-to or ship-to); joined to AR_LOOKUPS.
  • DQMEANING — the translated lookup meaning for DQSITE_USE_TYPE.
  • DQSTATUS — raw status code from the base record, with NULLs implicitly treated as active.
  • DQSTATUSM — decoded status text derived from the REGISTRY_STATUS lookup type.
  • DQBEGIN_DATE / DQEND_DATE — effective date range for the site use.
  • DQPRIMARY_PER_TYPE — flag indicating whether the use is the primary one for its type.
  • DQCOMMENTS — free-text comments attached to the site use.

Common Use Cases and Queries

Typical scenarios include data quality dashboards that must exclude merged records, duplicate-detection scripts comparing active party site uses across parties, and integration extracts feeding downstream systems with decoded status and site-use values.

List all active party site uses with decoded meanings:

  • SELECT dqparty_site_use_id, dqparty_site_id, dqmeaning, dqstatusm FROM hz_dqm_ps_u_v WHERE dqstatus = 'A';

Identify primary uses per type for a given site:

  • SELECT dqparty_site_use_id, dqsite_use_type, dqprimary_per_type FROM hz_dqm_ps_u_v WHERE dqparty_site_id = :p_site_id AND dqprimary_per_type = 'Y';

Review uses created within a date range for cleansing purposes:

  • SELECT dqparty_site_use_id, dqbegin_date, dqcomments FROM hz_dqm_ps_u_v WHERE dqbegin_date >= :p_from_date ORDER BY dqbegin_date;

Because the view applies the STATUS 'M' exclusion and relies on lookup joins, queries should not assume that every base party site use is returned, and consumers requiring merged records must query HZ_PARTY_SITE_USES directly.