My Oracle Support Banner

E-QR: Blank Datetime Field Values Are Displayed As "#########################" In The Excel Output File Generated (Doc ID 1983320.1)

Last updated on FEBRUARY 04, 2019

Applies to:

PeopleSoft Enterprise PT PeopleTools - Version 8.54 and later
Information in this document applies to any platform.

Symptoms

When attempting to run queries with blank Date field values, the Excel output file shows "########################" pound signs, then moving the mouse cursor inside the cell it shows the "-1" value and shows the message "Date and Times that are negatives or too large displays as ################# "

The issue is specific to only PIA and not to Application Designer. The issue does not happen when downloading the PS Query output results to CSV.


Steps
--------
The issue can be reproduced at will with the following steps:

There are more scenarios when the issue may occur:

      ***  Test Case 1 ***

1. Login to PIA

2. Navigate to Reporting Tools > Query > Query Manager

3. Create a new PS Query (SB_BLANK_DATETIME) with TIMESTAMP field
    SELECT
A.BUSINESS_UNIT,
A.TRANSACTION_GROUP,
TO_CHAR(A.ACCOUNTING_DT,'YYYY-MM-DD'),
A.ACCTG_LINE_NO,
TO_CHAR(CAST((A.CM_DT_TIMESTAMP) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF')
    FROM PS_CM_ACCTG_LINE A

4. Go to Run tab

5. Run the Query to HTML

6. Download the Query to Excel

7. There are blank values in the CM_DT_TIMESTAMP field which DOES NOT displays correctly in the output Excel file pound signs are displayed instead of blank values. Then moving the mouse cursor inside the cell  it shows the "-1" value and shows the message "Date and Times that are negatives or too large displays as ################"

8. Run the query to CSV - the issue may not be reproduced - used the navigation Reporting Tools > Query > Query Manager - Run the Query to HTML, then download the output results to CSV -  click on "CSV Text File" hyperlink.

      ***  Test Case 2 ***

When running Query to Excel through an Application Engine using  Runtofile method with %Query_XLS option , the Excel output returned is empty (0 kb file)

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.