Search Results get_avg_days_late

Overview

IEX_COLL_IND is an Oracle EBS Advanced Collections (IEX) PL/SQL package that exposes a set of indicator and metric calculation routines used to quantify a customer's collection performance and credit exposure. The package is owned by APPS and is declared AUTHID CURRENT_USER, meaning its unqualified references to database objects resolve against the calling schema's privileges rather than the definer's. This design supports invocation from forms, concurrent programs, and other PL/SQL units that operate under a session whose security context already grants access to the underlying AR and HZ objects.

Functionally, the package computes collection indicators such as weighted days late, average days late, Days Sales Outstanding (DSO) variants, non-sufficient-funds and stop-payment statistics, sales and deduction totals, and aggregate receivables balances. These indicators feed collection scorecards, customer profile evaluations, and collector worksheets. The header comment notes that the system parameter record can be modified based on the information required, indicating the package is designed to adapt its currency and date-basis context at runtime. The package header is versioned 120.2 dated September 2011, placing it in the shared lineage used across EBS 12.1.1 and 12.2.2.

Key Procedures and Functions

The documented interface comprises 32 routines. The most prominent are the indicator functions, all of which share a consistent customer-context addressing scheme driven by the p_party_id, p_cust_account_id, and p_customer_site_use_id parameters:

• GET_WTD_DAYS_LATE / GET_WTD_DAYS_PAID / GET_WTD_DAYS_TERMS — Compute weighted-average days late, weighted-average days paid, and weighted-average terms respectively for the specified customer context.
• GET_AVG_DAYS_LATE — Returns the average days late indicator, complementing the weighted measures.
• GET_CEI — Returns the Collection Effectiveness Index, a standard measure of collection performance.
• GET_TRUE_DSO / GET_CONV_DSO — Return true and conventional Days Sales Outstanding metrics.
• GET_NSF_STOP_PMT_COUNT / GET_NSF_STOP_PMT_AMOUNT — Count and value of non-sufficient-funds and stop-payment events attributed to the customer.
• GET_SALES / GET_DEDUCTION — Aggregate sales and deduction amounts used as inputs to the ratios above.
• COMP_TOT_REC, COMP_REM_REC, COMP_CURR_REC — Component totals for receivables: total, remaining, and current receivables.
• GET_APPS_TOTAL / GET_ADJ_TOTAL / GET_ADJ_FOR_TOT_REC — Application totals, adjustment totals, and adjustments applied against total receivables.
• GET_CREDIT_LIMIT / GET_HIGH_CREDIT_YTD — Credit limit and year-to-date high credit exposure indicators.
• AMOUNT_DUE_ORG_OCM — Organizational amount-due calculation, likely supporting an org-level or OCM reporting context.

The routines are declared in the package specification with p_party_id, p_cust_account_id, and p_customer_site_use_id as the standard parameter triad, and each returns VARCHAR2. The record type curr_rec_type and global g_curr_rec carry set of books, base currency, precision, minimum accountable unit, and past-year-from/to dates, providing the currency and period context for the calculations.

Tables Accessed

The package reads from AR system and transaction tables and from HZ customer tables through APPS synonyms:

• AR_SYSTEM_PARAMETERS, FND_CURRENCIES, GL_SETS_OF_BOOKS — Source of set of books, base currency, and currency precision/minimum accountable unit used to populate curr_rec_type.
• AR_PAYMENT_SCHEDULES / AR_PAYMENT_SCHEDULES_ALL — Core source for due dates, payment terms, and remaining balances underlying days-late and DSO calculations.
• AR_CASH_RECEIPTS, AR_CASH_RECEIPTS_ALL, AR_CASH_RECEIPT_HISTORY, AR_CASH_RECEIPT_HISTORY_ALL — Provide payment and receipt history for days-paid, NSF, and stop-payment indicators.
• AR_RECEIVABLE_APPLICATIONS — Supplies application amounts used for application totals and adjustment analysis.
• AR_ADJUSTMENTS — Source of adjustment amounts for deduction and adjustment indicators.
• AR_TRX_BAL_SUMMARY, AR_TRX_SUMMARY — Summary balances and transaction aggregates supporting receivables and sales totals.
• HZ_CUST_ACCOUNTS, HZ_CUSTOMER_PROFILES, HZ_CUST_PROFILE_AMTS — Customer identification, profile credit limits, and profile-level amounts used for credit-limit and high-credit indicators.

Usage Notes

IEX_COLL_IND is referenced by three other packages, indicating it functions as a shared indicator library within the Advanced Collections dependency tree rather than as an entry-point API. It is typically invoked from Advanced Collections forms, collector worksheets, and scorecard logic, and may be called from concurrent programs and custom code that require standardized collection metrics for a given party, customer account, and site use context.

Because the addressing parameters are p_party_id, p_cust_account_id, and p_customer_site_use_id, callers searching on p_party_id should note that this parameter identifies the party (TCA party), while the customer account and site use identifiers refine the calculation scope. The functions return VARCHAR2 values and therefore require careful numeric conversion by the caller. Callers should supply a consistent customer context across the three parameters; passing null or mismatched values may yield context-spanning or partial results. The routines perform read-only aggregation against AR and HZ data.