IEX Collection Note Upload

➜ Back to Library
Description

IEX Collection Note Upload creates and updates Oracle Advanced Collections notes from Excel – the same notes collectors see in the Collections work area. Each note is attached at one Note Level: Party, Account, Bill To or Collections Transaction. For transaction notes, the upload can also record an Unpaid Reason, creating the collections delinquency it needs.

When to use it

  • Log the outcome of a collections call campaign or dunning run as notes on many customers at once.
  • Record notes against specific open invoices, with the customer’s unpaid reason.
  • Migrate collection history notes from a legacy or third-party collections system.
  • Reclassify existing notes by changing their Note Type or Note Status in bulk.

Before you start

  • Access to the upload in Blitz Report, with a responsibility that can see the operating units of the accounts and transactions.
  • The customers, accounts, bill-to sites and open transactions the notes belong to exist in Oracle Receivables.

Step 1 – Set the parameters

Open IEX Collection Note Upload in Blitz Report and set the parameters:

ParameterMeaning
Upload ModeCreate (default) opens an empty sheet for new notes; Create, Update downloads existing notes for editing and lets you add new ones.
Party / Party NumberDownloads the notes of this customer at every level (party, its accounts, bill-to sites and transactions).
Account NumberRestricts the download to notes of this account and its sites and transactions.
Operating UnitRestricts bill-to and transaction notes to this operating unit. Party and account notes are always included.
Bill To SiteRestricts the download to notes of this bill-to site and its transactions.
Transaction NumberRestricts the download to notes of this transaction.
Note LevelDownloads only notes of this level: Party, Account, Bill To or Collections Transaction.
Note Date From / Note Date ToThe entered date range of the notes to download. Both default to today, so widen the range to download older notes.
Blitz Report run screen for IEX Collection Note Upload with Upload Mode Create, Update, Party Hilman and Associates and Note Date From 01-NOV-2004

Step 2 – Run to download the Excel file

Click Run. The Excel file downloads and opens with one row per existing note, or an empty sheet in Create mode.

Excel file with the four existing party level notes of Hilman and Associates

Step 3 – Enter or change the notes

For a new note, enter the columns that identify its target and pick the Note Level, which lists only the level matching the columns you filled:

Note LevelColumns to enter
PartyParty (Party Number only when the party name is not unique). Leave Account Number blank.
AccountParty and Account Number.
Bill ToParty, Account Number and Bill To Site. Operating Unit only when the same bill-to location exists in several operating units.
Collections TransactionParty, Account Number and Transaction Number of an open transaction. Bill To Site and Operating Unit only when the transaction number is not unique for the account; Installment when the transaction has several installments.

Then enter the Note (up to 2,000 characters), an optional Note Detail for longer text (cut at 4,000 characters), the Note Type and the Note Status (defaults to Public). Entered Date defaults to the current date and time. Bill To Address and Delinquency Status are filled in for information.

On a new Collections Transaction note you can also set an Unpaid Reason. Collections holds it on the transaction’s delinquency, not on the note, so it applies to every note of that transaction. If Delinquency Status is blank the transaction has no delinquency yet: set Create Delinquency to Yes to create one with status Current, as the Collections form asks.

On a downloaded note only the Note Type, Note Status and descriptive flexfield columns can be changed; the note text and its target are fixed.

Excel file with the Note Type of one party note changed, marked Update, and a new Collections Transaction note for transaction 518788 with Unpaid Reason Incorrect Bill and Create Delinquency Yes, marked Create

Step 4 – Validate and Save

Click Validate and Save. This checks for missing required values and saves the file. Correct any rows it flags before uploading.

Both edited rows show status Valid after Validate and Save

Step 5 – Upload the file

In Blitz Report click Upload and select the saved file. This submits the upload request, which creates or updates each note.

File Upload page with the saved Excel file selected for upload

Step 6 – Review the result report

When the request completes, a result report opens listing every uploaded row with its status and message, including the bill-to site, operating unit and delinquency status the note was attached to.

Result report showing the updated party note and the created transaction note with status Success, its delinquency created with status Current and Unpaid Reason set

What’s produced

  • Collection notes created at the chosen level, or existing notes with a changed type, status or flexfield values. A note is also linked to the levels above it (bill-to site, account, party) and to the customer’s collections contact, so it shows at each of those levels in Collections.
  • For transaction notes with an Unpaid Reason: the reason stored on the transaction’s delinquency, and the delinquency created when Create Delinquency is Yes.
  • A result report listing every row with a status (success or error) and a message.

Common questions

Can I change the text of an existing note?
No. Only Note Type, Note Status and the descriptive flexfield can be changed on an existing note. Add a new note instead.

Can I delete notes?
No. The upload only creates and updates notes.

Why can’t I set an Unpaid Reason on a downloaded note?
The unpaid reason, like Create Delinquency, can only be set when a note is created, as in the Collections notes window.

Why does the Note Level list show only one value?
It lists the level that matches the identifying columns entered on the row. To attach a note to an account rather than the party, enter the Account Number first; to attach it to a transaction, enter the Transaction Number.

Can I add notes to closed transactions?
No. Transaction notes can only be attached to open transactions.

Troubleshooting

MessageCauseWhat to do
Party Name is not unique. Please enter the Party Number.Several active parties have this name.Enter the Party Number.
Customer with with this Party Name and/or Party Number not found or is not active.No active party matches the Party or Party Number.Pick the party from the list.
Customer with this Account Number not found for this customer or is not active.The account does not belong to the party or is inactive.Pick the Account Number from the list after entering the Party.
Bill To location is not unique for this account. Please enter the Operating Unit as well.The bill-to location exists in several operating units.Enter the Operating Unit.
Bill To location not found or is not active.No active bill-to site with this location for the account.Pick the Bill To Site from the list.
Transaction/Installment not found or is not open.The transaction or installment does not exist for the account, or is closed.Pick an open transaction from the list.
Transaction Number is not unique for this account. Please enter the Bill To Location and Operating Unit as well.Several transactions of the account share this number.Enter Bill To Site and Operating Unit.
Transaction has multiple installments. Enter the Installment to attach the note.The transaction has several open installments.Enter the Installment.
Unpaid Reason and Create Delinquency only apply to a Transaction note.Unpaid Reason or Create Delinquency entered on another note level.Clear them, or attach the note to a transaction.
Transaction is not delinquent. Set Create Delinquency to Yes to create a delinquency with status Current.Unpaid Reason entered for a transaction without a delinquency.Set Create Delinquency to Yes.
Note exceeds 2000 characters. Use the Note Detail column for longer text.The Note is longer than 2,000 characters.Shorten the Note and put the rest in Note Detail.
Note ID is required for update. Download the note before updating it.An Update row that was not downloaded.Download the note in Create, Update mode and change it there.
select
null action_,
null status_,
null message_,
null modified_columns_,
to_number(null) jtf_note_id_out,
hp.party_name party,
hp.party_number party_number,
hca.account_number,
hcsua.location bill_to_site,
haouv.name operating_unit,
hz_format_pub.format_address(hps.location_id,null,null,', ') bill_to_address,
rcta.trx_number transaction_number,
to_char(aps.terms_sequence_number) installment,
jov.name note_level,
xxen_util.meaning(ida.status,'IEX_DELINQUENCY_STATUS',695) delinquency_status,
to_char(null) create_delinquency,
xxen_util.meaning(ida.unpaid_reason_code,'IEX_UNPAID_REASON',695) unpaid_reason,
x.entered_date,
xxen_util.meaning(x.note_type,'JTF_NOTE_TYPE',0) note_type,
xxen_util.meaning(x.note_status,'JTF_NOTE_STATUS',0) note_status,
x.notes note,
dbms_lob.substr(xxen_util.clob_substrb(x.notes_detail,4000),4000,1) note_detail,
xxen_util.display_flexfield_context(690,'COM_FLEX',x.context) attribute_category,
xxen_util.display_flexfield_value(690,'COM_FLEX',x.context,'ATTRIBUTE1',x.row_id,x.attribute1) attribute1,
xxen_util.display_flexfield_value(690,'COM_FLEX',x.context,'ATTRIBUTE2',x.row_id,x.attribute2) attribute2,
xxen_util.display_flexfield_value(690,'COM_FLEX',x.context,'ATTRIBUTE3',x.row_id,x.attribute3) attribute3,
xxen_util.display_flexfield_value(690,'COM_FLEX',x.context,'ATTRIBUTE4',x.row_id,x.attribute4) attribute4,
xxen_util.display_flexfield_value(690,'COM_FLEX',x.context,'ATTRIBUTE5',x.row_id,x.attribute5) attribute5,
xxen_util.display_flexfield_value(690,'COM_FLEX',x.context,'ATTRIBUTE6',x.row_id,x.attribute6) attribute6,
xxen_util.display_flexfield_value(690,'COM_FLEX',x.context,'ATTRIBUTE7',x.row_id,x.attribute7) attribute7,
xxen_util.display_flexfield_value(690,'COM_FLEX',x.context,'ATTRIBUTE8',x.row_id,x.attribute8) attribute8,
xxen_util.display_flexfield_value(690,'COM_FLEX',x.context,'ATTRIBUTE9',x.row_id,x.attribute9) attribute9,
xxen_util.display_flexfield_value(690,'COM_FLEX',x.context,'ATTRIBUTE10',x.row_id,x.attribute10) attribute10,
xxen_util.display_flexfield_value(690,'COM_FLEX',x.context,'ATTRIBUTE11',x.row_id,x.attribute11) attribute11,
xxen_util.display_flexfield_value(690,'COM_FLEX',x.context,'ATTRIBUTE12',x.row_id,x.attribute12) attribute12,
xxen_util.display_flexfield_value(690,'COM_FLEX',x.context,'ATTRIBUTE13',x.row_id,x.attribute13) attribute13,
xxen_util.display_flexfield_value(690,'COM_FLEX',x.context,'ATTRIBUTE14',x.row_id,x.attribute14) attribute14,
xxen_util.display_flexfield_value(690,'COM_FLEX',x.context,'ATTRIBUTE15',x.row_id,x.attribute15) attribute15,
xxen_util.user_name(x.created_by) created_by,
to_number(null) upload_row,
x.jtf_note_id
from
(
select
jnv.jtf_note_id,
jnv.source_object_code,
jnv.source_object_id,
jnv.note_type,
jnv.note_status,
jnv.notes,
jnv.notes_detail,
jnv.entered_date,
jnv.created_by,
jnv.context,
jnv.row_id,
jnv.attribute1,
jnv.attribute2,
jnv.attribute3,
jnv.attribute4,
jnv.attribute5,
jnv.attribute6,
jnv.attribute7,
jnv.attribute8,
jnv.attribute9,
jnv.attribute10,
jnv.attribute11,
jnv.attribute12,
jnv.attribute13,
jnv.attribute14,
jnv.attribute15,
case jnv.source_object_code
when 'PARTY' then jnv.source_object_id
when 'IEX_ACCOUNT' then (select hca2.party_id from hz_cust_accounts hca2 where hca2.cust_account_id=jnv.source_object_id)
when 'IEX_BILLTO' then (select hca2.party_id from hz_cust_site_uses_all hcsua2,hz_cust_acct_sites_all hcasa2,hz_cust_accounts hca2 where hcsua2.site_use_id=jnv.source_object_id and hcasa2.cust_acct_site_id=hcsua2.cust_acct_site_id and hca2.cust_account_id=hcasa2.cust_account_id)
when 'IEX_INVOICES' then (select hca2.party_id from ar_payment_schedules_all aps2,ra_customer_trx_all rcta2,hz_cust_accounts hca2 where aps2.payment_schedule_id=jnv.source_object_id and rcta2.customer_trx_id=aps2.customer_trx_id and hca2.cust_account_id=rcta2.bill_to_customer_id)
end party_id,
case jnv.source_object_code
when 'IEX_ACCOUNT' then jnv.source_object_id
when 'IEX_BILLTO' then (select hcasa2.cust_account_id from hz_cust_site_uses_all hcsua2,hz_cust_acct_sites_all hcasa2 where hcsua2.site_use_id=jnv.source_object_id and hcasa2.cust_acct_site_id=hcsua2.cust_acct_site_id)
when 'IEX_INVOICES' then (select rcta2.bill_to_customer_id from ar_payment_schedules_all aps2,ra_customer_trx_all rcta2 where aps2.payment_schedule_id=jnv.source_object_id and rcta2.customer_trx_id=aps2.customer_trx_id)
end cust_account_id,
case jnv.source_object_code
when 'IEX_BILLTO' then jnv.source_object_id
when 'IEX_INVOICES' then (select rcta2.bill_to_site_use_id from ar_payment_schedules_all aps2,ra_customer_trx_all rcta2 where aps2.payment_schedule_id=jnv.source_object_id and rcta2.customer_trx_id=aps2.customer_trx_id)
end site_use_id,
case when jnv.source_object_code='IEX_INVOICES' then jnv.source_object_id end payment_schedule_id,
case jnv.source_object_code
when 'IEX_BILLTO' then (select hcasa2.org_id from hz_cust_site_uses_all hcsua2,hz_cust_acct_sites_all hcasa2 where hcsua2.site_use_id=jnv.source_object_id and hcasa2.cust_acct_site_id=hcsua2.cust_acct_site_id)
when 'IEX_INVOICES' then (select aps2.org_id from ar_payment_schedules_all aps2 where aps2.payment_schedule_id=jnv.source_object_id)
end org_id
from
jtf_notes_vl jnv
where
jnv.source_object_code in ('PARTY','IEX_ACCOUNT','IEX_BILLTO','IEX_INVOICES')
) x,
hz_parties hp,
hz_cust_accounts hca,
hz_cust_site_uses_all hcsua,
hz_cust_acct_sites_all hcasa,
hz_party_sites hps,
ar_payment_schedules_all aps,
ra_customer_trx_all rcta,
hr_all_organization_units_vl haouv,
jtf_objects_vl jov,
iex_delinquencies_all ida
where
hp.party_id(+)=x.party_id and
hca.cust_account_id(+)=x.cust_account_id and
hcsua.site_use_id(+)=x.site_use_id and
hcasa.cust_acct_site_id(+)=hcsua.cust_acct_site_id and
hps.party_site_id(+)=hcasa.party_site_id and
aps.payment_schedule_id(+)=x.payment_schedule_id and
rcta.customer_trx_id(+)=aps.customer_trx_id and
haouv.organization_id(+)=x.org_id and
jov.object_code(+)=x.source_object_code and
jov.application_id(+)=decode(x.source_object_code,'PARTY',690,695) and
ida.payment_schedule_id(+)=x.payment_schedule_id and
(x.org_id is null or x.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11)) and
1=1
Parameter NameSQL textValidation
Upload Mode
:p_upload_mode like '%'||xxen_upload.action_update
LOV
Party
x.party_id=:party
LOV
Party Number
x.party_id=:party_number
LOV
Account Number
x.cust_account_id=:account_number
LOV
Operating Unit
(x.org_id is null or haouv.name=:operating_unit)
LOV
Bill To Site
(x.site_use_id=:bill_to_site or rcta.bill_to_site_use_id=:bill_to_site)
LOV
Transaction Number
rcta.customer_trx_id=:transaction_number
LOV
Note Level
x.source_object_code=:note_level
LOV
Note Date From
x.entered_date>=:note_date_from
Date
Note Date To
x.entered_date<:note_date_to+1
Date
Download
Related Blitz Reports
AR Transaction Upload AR Aging APL: AR Transactions and Lines SDS AR Aging with document currency amounts Avalara VAT Report Galana AR Aging GL Account Distribution Analysis GL Account Analysis RK: AR Transactions and Lines
Blitz Report™