Results for “op_chargeback_value”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

IEX_DLN_UWQ_SUMMARY is a denormalized summary table in the IEX (Collections) schema of Oracle E-Business Suite, implemented under Advanced Collections / iReceivables / Trade Management (ETRM) functionality. Its documented description is "Summary table for universal work queue collections nodes." It exists to pre-aggregate, per delinquent customer account and per collector, the metrics that drive the Universal Work Queue (UWQ) collections dashboard nodes — aging buckets, disputed and past-due invoice values, promise-to-pay status, bankruptcy and delinquency counters, and scoring — so that collectors can view prioritized work items without incurring the cost of real-time joins across AR and Collections transaction tables.

The table is owned by the IEX schema, has status VALID, and contains 116 physical columns in the documented 12.2.2 schema. It carries the standard EBS WHO columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN), the multi-org selector ORG_ID, and the OAF concurrency column OBJECT_VERSION_NUMBER. Its ETRM documentation classifies it heuristically as a standalone Data Vault model: FK analysis shows no incoming dependents, meaning it is a consumer rather than a hub of referential integrity. As a modeling suggestion, this object behaves most like a satellite — a set of descriptive, time-stamped measures attached to a customer-account/collector business key — rather than a hub or link, though the classification should be treated as illustrative because the physical table is not normalized to Data Vault form.

Key Information Stored

The surrogate primary key is DLN_UWQ_SUMMARY_ID. Business-key candidates worth validating against the EBS data dictionary are the composite of ORG_ID, COLLECTOR_ID, and CUST_ACCOUNT_ID, since the grain is a collector-and-customer snapshot. The most consequential columns fall into four groups:

• Identity and context: ORG_ID, COLLECTOR_ID (FK to AR_COLLECTORS), COLLECTOR_RESOURCE_ID, COLLECTOR_RESOURCE_NAME, CUST_ACCOUNT_ID (FK to HZ_CUST_ACCOUNTS), PARTY_ID, PARTY_NAME, ACCOUNT_NAME, ACCOUNT_NUMBER, and SITE_USE_ID / LOCATION.
• Receivables exposure: OP_INVOICES_COUNT, OP_INVOICES_VALUE, OP_CREDIT_MEMOS_COUNT / VALUE, OP_DEBIT_MEMOS_COUNT / VALUE, OP_DEPOSITS_COUNT / VALUE, OP_CHARGEBACK_COUNT / VALUE, OP_BILLS_RECEIVABLES_COUNT / VALUE, BEST_CURRENT_RECEIVABLES, PAST_DUE_INV_VALUE, PAST_DUE_INV_INST_COUNT, INV_AMT_IN_DISPUTE, DISPUTED_INV_COUNT, PENDING_ADJ_VALUE, UNRESOLVED_CASH_COUNT / VALUE, RECEIPTS_AT_RISK_VALUE, and NET_BALANCE.
• Aging and payment behavior: AGING_AMOUNT1 through AGING_AMOUNT7 and AGING_COUNT1 through AGING_COUNT7 map to configured aging buckets; LAST_PAYMENT_DATE, LAST_PAYMENT_AMOUNT, LAST_PAYMENT_AMOUNT_CURR, and LAST_PAYMENT_NUMBER describe recent payment activity.
• Collection and risk indicators: NUMBER_OF_DELINQUENCIES, ACTIVE_DELINQUENCIES, PENDING_DELINQUENCIES, COMPLETE_DELINQUENCIES, NUMBER_OF_BANKRUPTCIES, NUMBER_OF_PROMISES, PROMISE_AMOUNT, BROKEN_PROMISE_AMOUNT, ACTIVE_PROMISES, PENDING_PROMISES, COMPLETE_PROMISES, SCORE, SCORE_ID, SCORE_NAME, BUSINESS_LEVEL, and CASES_COUNT / DEL_CASES_COUNT / CONTRACTS_COUNT / DEL_CONTRACTS_COUNT.

Work-item attributes such as WORK_ITEM_ID, WORK_ITEM_ORDER, WKITEM_RESOURCE_ID, SCHEDULE_START, SCHEDULE_END, WORK_TYPE, STATUS_CODE, STR_STATUS, and WKITEM_ESCALATED_YN support queue ordering and escalation, while STRATEGY_ID, STRATEGY_TEMPLATE_ID, and WORK_ITEM_TEMPLATE_ID tie the snapshot to the delivered collections strategy that produced it. Request/program columns (REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID) identify the concurrent program that generated the rows.

Common Use Cases and Queries

Typical usage centers on collector workload dashboards and delinquent-account reporting. A representative query to rank collectors by exposure within an operating unit:

SELECT collector_id, collector_resource_name, SUM(past_due_inv_value) past_due, SUM(inv_amt_in_dispute) disputed, SUM(best_current_receivables) current FROM iex.iex_dln_uwq_summary WHERE org_id = :p_org_id GROUP BY collector_id, collector_resource_name ORDER BY past_due DESC;

For promise and risk analysis, filtering on active promises and bankruptcy indicators supports prioritization: WHERE active_promises > 0 AND NUMBER_OF_BANKRUPTCIES > 0. Aging distribution reports join the seven AGING_AMOUNT/AGING_COUNT pairs to bucket definitions in the collections aging configuration, and daily trend reporting compares successive snapshots (or successive concurrent program runs keyed by REQUEST_ID) to track movement in delinquency and dispute balances. Because the table is pre-aggregated, it is best suited to RAC/Reports Publisher extracts, Discoverer workbooks, and OAF Collector Workbench queries rather than drill-down transaction reconciliation, which should be sourced from AR_PAYMENT_SCHEDULES and AR_CASH_RECEIPTS.

Related Objects

Documented foreign keys establish the two hard relationships: COLLECTOR_ID references AR_COLLECTORS, and CUST_ACCOUNT_ID references HZ_CUST_ACCOUNTS. These are the primary join points for building collector and customer descriptions. Related objects that interact with this summary in practice include:

• AR_COLLECTORS — collector definition, joined on COLLECTOR_ID.
• HZ_CUST_ACCOUNTS — customer account master, joined on CUST_ACCOUNT_ID; further joins to HZ_PARTIES via PARTY_ID populate PARTY_NAME.
• IEX_STRATEGIES / IEX_WORK_ITEMS — the collections strategy and work-item engine that generate and consume UWQ queue contents, referenced by STRATEGY_ID and WORK_ITEM_ID.
• IEX_CASE_FOLDERS and IEX_DELINQUENCIES — case and delinquency records aggregated here as CASES_COUNT and NUMBER_OF_DELINQUENCIES.
• AR_PAYMENT_SCHEDULES and AR_CASH_RECEIPTS — transactional sources behind the receivables and aging values, used for drill-down.
• IEU_* / JTF_* objects referenced through IEU_OBJECT_FUNCTION, IEU_OBJECT_PARAMETERS, IEU_MEDIA_TYPE_UUID, IEU_PARAM_PK_COL, and IEU_PARAM_PK_VALUE, which drive the interaction/media linkage for UWQ resources.

Because no dependent tables carry foreign keys back to IEX_DLN_UWQ_SUMMARY, it is a leaf reporting object: safe to query and summarize, but populated exclusively by the Collections concurrent programs identified by REQUEST_ID, PROGRAM_ID, and PROGRAM_APPLICATION_ID.