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

Last updated on MARCH 03, 2016

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)

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