Fusion Applications BI Publisher : Leading blank lines being displayed in report from NOTE_TXT field

(Doc ID 2103858.1)

Last updated on SEPTEMBER 21, 2016

Applies to:

Oracle Fusion Sales Cloud Service - Version 11.1.9.2.0 and later
Oracle Fusion Application Toolkit Cloud Service - Version 11.1.10.0.0 and later
Information in this document applies to any platform.

Symptoms

A query was being issued from a SQL-based BI Publisher data model against a large text field in the ZMM_NOTES table, the NOTE_TXT field.

When data from this field is viewed in the UI it displays as expected with no blank lines and from the top left of the dialog however once this same field was retrieved into a report the output showed a leading blank line in front of the NOTE_TXT data.

Note_txt from the table ZMM_NOTES
Issue: when you enter text, in the UI it displays from the left of the screen on the first line but in the query it shows from the next line.
Troubleshooting done: have tried to use replace along with char 9, 10,13 as suggested in some of the community posts but is not removing the first line.
Query used:
SELECT NOTE_ID,replace(replace(TRIM(NOTE_TXT),CHR(10),' '),CHR(13),' ') AS Note_Text FROM ZMM_NOTES

What we are looking for:
we need the data as entered in the UI ie. from the first line instead of second line is there any other way to remove the blank first line from the data ?

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms