E-CR: PT8.54 Crystal Reports With Date Prompt Errors Out With 'Invalid Date Format' (SQL Server) or ORA-01841 (Oracle) (Doc ID 1955120.1)

Last updated on AUGUST 19, 2015

Applies to:

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

Symptoms

Crystal reports with date prompts are consistently failing with error

Crystal reports without date prompts do not give the error.

Replication Steps:
Issue is replicated in-house on Crystal Reports 2008 SP3 on PT8.54.02 on database MS SQL Server 2012 CU3.
1. Open CR2008 SP3 client.
2.  Open APY10200-.RPT
3.  Refresh Data and see error message

Environment/users:
PT8.54.x + Crystal Reports 2008 SP3
OS: Windows 2008 R2 64-bit, Windows 7 64-bit

 

SQL Server 2012  Error Message:
File: E:\pt85402e-retail\peopletools\src\pssys\qpm.cppSQL error. Stmt #: 7228  Error Position: 0  Return: 8601 - [Microsoft][SQL Server Native Client 10.0]Invalid date format (SQLSTATE 22008) 0
Failed SQL stmt: SELECT A.BUSINESS_UNIT, A.VOUCHER_ID, B.VENDOR_ID, C.NAME1, (CONVERT(CHAR(10),A.ACCOUNTING_DT,121)), A.APPL_JRNL_ID, A.DST_ACCT_TYPE, A.VOUCHER_LINE_NUM, A.DISTRIB_LINE_NUM, A.BUSINESS_UNIT_GL, A.ACCOUNT, A.ALTACCT, A.OPERATING_UNIT, A.FUND_CODE, A.DEPTID, A.PROGRAM_CODE, A.CLASS_FLD, A.BUDGET_REF, A.PRODUCT, A.PROJECT_ID, A.AFFILIATE, A.AFFILIATE_INTRA1, A.AFFILIATE_INTRA2, A.FOREIGN_CURRENCY, A.FOREIGN_AMOUNT, A.MONETARY_AMOUNT, A.UNPOST_SEQ, 'NULL      ', 'NULL      ', A.CURRENCY_CD, A.DOC_TYPE, (CONVERT(CHAR(10),A.DOC_SEQ_DATE,121)), A.DOC_SEQ_NBR, A.LEDGER, A.JOURNAL_ID, (CONVERT(CHAR(10),B.INVOICE_DT,121)), A.PYMNT_CNT    FROM PS_VCHR_ACCTG_LINE A, PS_VOUCHER B, PS_VENDOR C, PS_RUN_CNTL_AP D    WHERE ( B.BUSINESS_UNIT = A.BUSINESS_UNIT      AND B.VOUCHER_ID = A.VOUCHER_ID      AND D.OPRID = 'VP1'      AND ( C.SETID = B.VENDOR_SETID       AND C.VENDOR_ID = B.VENDOR_ID       AND A.BUSINESS_UNIT = :1       AND A.ACCOUNTING_DT BETWEEN :2 AND :3       AND B.POST_STATUS_AP = 'P'       AND D.OPRID = :4       AND D.RUN_CNTL_ID = :5       AND C.VENDOR_PERSISTENCE <> 'S'       AND  ( D.VENDOR_SELECT_OPTN = 'A'       OR  ( D.VENDOR_SELECT_OPTN = 'S'       AND B.VENDOR_ID IN (SELECT F.VENDOR_ID    FROM PS_RUN_CNTL_AP E, PS_RUN_CNTL_AP_VN3 F    WHERE E.OPRID = 'VP1'      AND F.OPRID = 'VP1'      AND ( E.OPRID = F.OPRID       AND E.RUN_CNTL_ID = F.RUN_CNTL_ID       AND E.OPRID = :6       AND E.RUN_CNTL_ID = :7)) )) ))  UNION  SELECT G.BUSINESS_UNIT, G.VOUCHER_ID, H.VENDOR_ID, I.NAME1, (CONVERT(CHAR(10),G.ACCOUNTING_DT,121)), G.APPL_JRNL_ID, G.DST_ACCT_TYPE, G.VOUCHER_LINE_NUM, G.DISTRIB_LINE_NUM, G.BUSINESS_UNIT_GL, G.ACCOUNT, G.ALTACCT, G.OPERATING_UNIT, G.FUND_CODE, G.DEPTID, G.PROGRAM_CODE, G.CLASS_FLD, G.BUDGET_REF, G.PRODUCT, G.PROJECT_ID, G.AFFILIATE, G.AFFILIATE_INTRA1, G.AFFILIATE_INTRA2, G.FOREIGN_CURRENCY, G.FOREIGN_AMOUNT, G.MONETARY_AMOUNT, G.UNPOST_SEQ, 'NULL      ', 'NULL      ', G.CURRENCY_CD, G.DOC_TYPE, (CONVERT(CHAR(10),G.DOC_SEQ_DATE,121)), G.DOC_SEQ_NBR, G.LEDGER, G.JOURNAL_ID, (CONVERT(CHAR(10),H.INVOICE_DT,121)), G.PYMNT_CNT    FROM PS_VCHR_ACCTG_LINE G, PS_VOUCHER H, PS_VCHR_VNDR_INFO I, PS_RUN_CNTL_AP J    WHERE ( J.OPRID = 'VP1'      AND ( G.BUSINESS_UNIT = H.BUSINESS_UNIT       AND G.VOUCHER_ID = H.VOUCHER_ID       AND H.BUSINESS_UNIT = I.BUSINESS_UNIT       AND H.VOUCHER_ID = I.VOUCHER_ID       AND G.BUSINESS_UNIT = :8       AND G.ACCOUNTING_DT BETWEEN :9 AND :10       AND H.POST_STATUS_AP = 'P'       AND J.OPRID = :11       AND J.RUN_CNTL_ID = :12       AND  ( J.VENDOR_SELECT_OPTN = 'A'       OR ( J.VENDOR_SELECT_OPTN = 'S'       AND H.VENDOR_ID IN (SELECT L.VENDOR_ID    FROM PS_RUN_CNTL_AP K, PS_RUN_CNTL_AP_VN3 L    WHERE K.OPRID = 'VP1'      AND L.OPRID = 'VP1'      AND ( K.OPRID = L.OPRID       AND K.RUN_CNTL_ID = L.RUN_CNTL_ID       AND K.OPRID = :13       AND K.RUN_CNTL_ID = :14)) )) ))    ORDER BY 1, 2, 6, 27, 7

 

Oracle 11.2.0.4 Error Message:

File: E:\pt85405c-retail\peopletools\src\pssys\qpm.cppSQL error. Stmt #: 7294  Error Position: 1196  Return: 1841 - ORA-01841: (full) year must be between -4713 and +9999, and not be 0 ....[more]

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