IEX Collection Note Upload – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.

Overview

Creates and updates Collection Notes in Oracle Collections (IEX) - the same notes shown in the Collections work area.

Each note is attached at a single Note Level: Customer (party), Account, Bill To, or Collections Transaction.

The Note column is limited to 2,000 characters. The upload will reject the row if the note exceeds this. The Note Detail column is limited to 4,000 characters. Note details exceeding this will be truncated.

Party Level Notes: Party Name is required. Party Number is required only when the Party Name is not unique.

Account Level Notes: Party Name and Account Number are required.

Bill To Level Notes: Party Name, Account Number and Bill To Location are required. Operating Unit is optional unless the same Bill To Location (code) is used across accessible Operating Units. Bill to Address is informational only.

Transaction Level Notes: Party Name, Account Number, Transaction Number are required. Bill To Site and Operating Unit are only required if the same transaction number exists across different accessible Bill To Sites/Operating Units. Installment is optional unless the Transaction has multiple Installments, in which case it must be specified.

Unpaid Reason applies to Transaction level notes only. Oracle Collections holds it on the transaction’s delinquency rather than on the note, so it applies to every note on that transaction. Delinquency Status shows whether the transaction is already delinquent. When it is blank, set Create Delinquency to Yes to create a delinquency with status Current, as the Collections form asks. Unpaid Reason and Create Delinquency can only be set when the note is created.

Only the Note Type, Note Status and descriptive flexfield attributes can be changed on an existing note; the note text and its target are read only. Download notes in Create, Update mode to reclassify them.

Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.

Report Parameters

Upload Mode, Party, Party Number, Account Number, Operating Unit, Bill To Site, Transaction Number, Note Level, Note Date From, Note Date To

Oracle EBS Tables Used

hz_cust_accounts, hz_cust_site_uses_all, hz_cust_acct_sites_all, ar_payment_schedules_all, ra_customer_trx_all, jtf_notes_vl, hz_parties, hz_party_sites, hr_all_organization_units_vl, jtf_objects_vl, iex_delinquencies_all, xxen_report_org_access

Report Categories

Enginatics, R12 only, Upload

AR Transaction Upload, AR Aging, AR Transactions and Lines, AR Transactions and Lines 11i, GL Account Distribution Analysis, GL Account Analysis, AR Customer Upload, GL Account Analysis (Drilldown) (with inventory and WIP), IEX Dunning History

Running This SQL Without Blitz Report

The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.

Download & Import Options

Resource Link
Excel Example Output Example
Blitz Report™ XML Import IEX_Collection_Note_Upload.xml
SQL Source IEX Collection Note Upload.sql
Full Report on Enginatics www.enginatics.com/reports/iex-collection-note-upload/

Case Study & Technical Analysis: IEX Collection Note Upload

Executive Summary

The IEX Collection Note Upload creates and updates Collection Notes in Oracle Collections, the same notes that appear in the Collections work area. Each note is attached to one of four note levels: the customer party, the account, the bill-to site or a collections transaction. Credit and collections teams use the upload to load call notes, promise-to-pay records and dispute comments in bulk, to correct note types and statuses, and to set up delinquencies on transactions. Blitz Report downloads existing notes so they can be reclassified, and the upload writes the changes back through the JTF notes and Collections tables.

Business Challenge

The Solution

Technical Architecture (High Level)

Primary Tables

Logical Relationships

  1. Notes are read from JTF_NOTES_VL where the source object code is PARTY, IEX_ACCOUNT, IEX_BILLTO or IEX_INVOICES.
  2. Each note’s party, account, bill-to site and payment schedule are derived from its source object by a CASE expression on the source object code.
  3. The derived party, account and site are joined to HZ_PARTIES, HZ_CUST_ACCOUNTS and the site tables, which supply the party name, party number, account number and bill-to location.
  4. For transaction notes, the payment schedule joins to RA_CUSTOMER_TRX_ALL for the transaction number, and IEX_DELINQUENCIES_ALL is joined on the payment schedule id.
  5. The note level name comes from JTF_OBJECTS_VL, and the operating unit is limited to the organizations in MO_GLOB_ORG_ACCESS_TMP.

Parameters & Filtering

Performance & Optimization

FAQ

Q: How do I add a collection note to a customer account in Oracle Collections? A: Set the Note Level to the account level, enter the Party Name and Account Number, and add the note text. The note is created against the account and appears in the Collections work area.

Q: Can I change the text of an existing collection note through the upload? A: No. Only the note type, note status and descriptive flexfield attributes can be changed on an existing note. The note text and its target are read only.

© Enginatics GmbH