E-BI/XMLP: Formatting Of Date fields In BI/XML Publisher Winword Template Sets The Date To -1 Day
Last updated on MAY 15, 2017
Applies to:PeopleSoft Enterprise PT PeopleTools - Version 8.48 and later
Information in this document applies to any platform.
When using the Winword Template, the BI Publisher, (XML Publisher), formatting of Date fields causes the actual date to be changed by -1.
- Problem shows only when run via Query Report Viewer, Query Report Scheduler, or Process Scheduler.
- Problem does not show during the Winword Preview or Report Definition Template Preview steps.
- Problem did not manifest in PT8.48.13.
- Problem did not manifest in PT8.49.06.
Steps followed to reproduce:
Verify Date formatting works on a default report using the same field PSTREEDEFN.EFFDT.
1) Login and create a new PSQuery in Query Manager using table PSTREEDEFN.EFFDT.
a) ReportingTools > Query > Query Manager.
b) Select fields TREE_NAME, EFFDT.
c) Save As PSTREEDEFN.
d) Run to HTML to confirm the EFFDT ==> result is 01/01/1980 for COMPETENCY.
2) Navigate to XMLPublisher and create a new XMLP Data Source for this PSQuery.
a) ReportingTools > XMLPublisher > Data Source > Add a new value.
b) Generate the XML and XSD files and save them to the desktop.
c) Save the new Data Source.
3) On the desktop, open Winword and create a simple report using PSTREEDEFN.
a) Open Winword.
b) Data > Load XML Data > select the PSTREEDEFN.xml file from your desktop, which was downloaded in step 1 above.
c) Insert > All Fields.
d) Highlight and delete the 4 header lines and leave the 4 columns, which have been inserted.
e) Double click on the EFFDT field and when it opens make the following changes: Type=Date, Default=January 01, 1901, DateFormat=MMMM d, yyyy, Bookmark=text21, Max length=Unlimited, Click OK.
f) Save the report as PSTREEDEFN.rtf on your desktop.
g) XMLP MENU Preview > PDF ==> result is Effdt column fomatted as "July 18, 2008".
h) Save and close the report.
4) Create a new PSTREEDEFN report using the template and the data source.
a) Navigate to ReportingTools > XMLPublisher > Report Definition > Add a new value.
b) Report Description=PSTREEDEFN, Report Category=ALLUSER.
c) Click on Template Tab, Upload the PSTREEDEFN.rtf, Change the status=Active.
d) On Definition Tab, Change the status=Active, Save.
e) On Template Tab, Preview > PDF ==> result is Effdt column fomatted as "July 18, 2008".
5) Run the report using XMLP Query Viewer ==> result is December 31, 1979 for TREE_NAME=COMPETENCY.
6) Run the report using XMLP Query Scheduler ==> result is December 31, 1979 for TREE_NAME=COMPETENCY.
Even if the EFFDT DATE formatting is removed, as long as this field is defined as a DATE field in Winword, the date is incorrect when run through the Query Report Viewer and Query Report Scheduler - it is still adjusted by -1 day.
If the EFFDT field is returned to Regular Text, the date is correct.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms