Search Results get_del_count

Overview

IEX_UWQ_VIEW_PKG is an Oracle Advanced Collections (IEX) PL/SQL package that supplies the aggregated view-layer figures displayed within the Unified Work Queue (UWQ) and related collector-facing screens. Its role is to centralize the "view" calculations that the Collections user interface and integration layers require when presenting a consolidated picture of a customer's collection position across parties, customer accounts, and site uses.

The package is declared with AUTHID CURRENT_USER, meaning its database objects and unqualified references resolve against the invoker's schema and privileges rather than the definer's. Within Oracle EBS 12.1.1 and 12.2.2, this makes it a reusable server-side read API rather than a self-contained business transaction processor. The header comment records that the bulk of the package was introduced under Bug 6634879 (November 2007), so the current signature set is stable across both release families.

Key Procedures and Functions

The documented interface exposes eleven functions, each returning a scalar value for a specific customer context.

• GET_DEL_COUNT — returns a count of delinquency records for the supplied party, account, site use, and UWQ status.
• GET_PRO_COUNT — returns a count of promise-type records, additionally scoped by operating unit (org_id).
• GET_STR_COUNT — returns a count of strategy-related records for the same customer context.
• CONVERT_AMOUNT — converts an amount from a source currency into the functional currency for display purposes.
• GET_LAST_PAYMENT_AMOUNT — returns the amount of the most recent payment for the customer context.
• GET_LAST_PAYMENT_NUMBER — returns the reference number of that most recent payment as a character value.
• GET_SCORE — returns the current collection score for the party/account/site use combination.
• GET_BROKEN_PRM_AMT — returns the value of promise amounts that were broken, scoped by operating unit.
• GET_PRM_AMT — returns the value of promise amounts for the customer context, scoped by operating unit.
• GET_CASE_COUNT — returns a count of collection cases for the supplied party, account, site use, and UWQ status. This is the function most often referenced when the Unified Work Queue needs to display the number of open cases associated with a customer row.
• GET_CONTRACT_COUNT — returns a count of contracts associated with the same customer context.

Each function takes identifiers (party, customer account, site use) and, where relevant, a UWQ status or org_id, always returning a single number or varchar2 suitable for direct display.

Tables Accessed

The package reads through APPS synonyms across several Advanced Collections and Receivables base tables:

• IEX_DELINQUENCIES_ALL — source for delinquency counts.
• IEX_PROMISE_DETAILS — source for promise amounts and broken promise calculations.
• IEX_SCORE_HISTORIES — source for the current collection score.
• OKC_K_HEADERS_B — source for contract counts.
• HZ_CUST_ACCOUNTS — customer account context.
• AR_SYSTEM_PARAMETERS — provides functional currency and Receivables setup used in amount conversion.
• FND_USER — resolves the current EBS user context.
• JTF_RS_RESOURCE_EXTNS — links the logged-in resource to collector or agent attributes.

All access is read-only; the package does not commit transactional data.

Usage Notes

IEX_UWQ_VIEW_PKG is typically invoked by the Advanced Collections Unified Work Queue user interface, related OAF or Forms collecter screens, and custom extensions that need to reproduce UWQ-style summary figures. Because it is one of the referenced packages in the EBS dependency chain, callers should treat it as an API to be called rather than modified. Performance is heavily dependent on the selectivity of the party, account, and site use arguments; custom code should pass fully qualified identifiers and avoid repeated per-row calls in tight loops where aggregate caching is possible.