E-QR: ORA-25156: Old Style Outer Join (+) Cannot Be Used With ANSI Joins

(Doc ID 2324822.1)

Last updated on NOVEMBER 03, 2017

Applies to:

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

Symptoms

In Tools 8.54 getting the error when the below query is run. "ORA-25156: old style outer join (+) cannot be used with ANSI joins".

 

SELECT A.BUSINESS_UNIT_IU, A.BUSINESS_UNIT, A.JOURNAL_ID, TO_CHAR(A.JOURNAL_DATE,'YYYY-MM-DD'), A.UNPOST_SEQ, A6X.XLATSHORTNAME,
TO_CHAR(A.REVERSAL_DATE,'YYYY-MM-DD'), TO_CHAR(A.JOURNAL_DATE_ORIG,'YYYY-MM-DD'), A.JRNL_TOTAL_LINES,
A.JRNL_TOTAL_DEBITS, A.JRNL_TOT_CREDITS, A.SOURCE, A.FOREIGN_CURRENCY, TO_CHAR(A.CUR_EFFDT,'YYYY-MM-DD'), A.DESCR254, A.DOC_TYPE, A.DOC_SEQ_NBR,
A.JOURNAL_CLASS, A.TRANS_REF_NUM, A.OPRID, A21X.XLATSHORTNAME, A.FISCAL_YEAR, A.ACCOUNTING_PERIOD, B.JOURNAL_LINE, B.ACCOUNT, C.DESCR,
B.ALTACCT, B.OPERATING_UNIT, B.DEPTID, B.PRODUCT, B.PROJECT_ID, B.CHARTFIELD1, B.CHARTFIELD2, B.AFFILIATE, B.SCENARIO, B.STATISTICS_CODE,
B.MONETARY_AMOUNT, B.STATISTIC_AMOUNT, B.JRNL_LN_REF, B.LINE_DESCR, B.CURRENCY_CD, B.FOREIGN_CURRENCY, B.FOREIGN_AMOUNT, B.LEDGER, A.RT_TYPE,
A.RATE_DIV, A.RATE_MULT, A.LEDGER_GROUP, B.RT_TYPE, B.RATE_DIV, B.RATE_MULT, B.MOVEMENT_FLAG, B.BOOK_CODE, A61X.XLATSHORTNAME, D.OPEN_ITEM_KEY
FROM PS_JRNL_HEADER A LEFT OUTER JOIN PSXLATITEM A6X ON A6X.FIELDNAME='REVERSAL_CD' AND A6X.FIELDVALUE=A.REVERSAL_CD AND
A6X.EFF_STATUS = 'A' AND A6X.EFFDT = (SELECT MAX(EFFDT) FROM PSXLATITEM TB
WHERE TB.FIELDNAME=A6X.FIELDNAME AND TB.FIELDVALUE=A6X.FIELDVALUE AND TB.EFF_STATUS = 'A' AND TB.EFFDT <=
TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') ) LEFT OUTER JOIN
PSXLATITEM A21X ON A21X.FIELDNAME='JRNL_HDR_STATUS' AND A21X.FIELDVALUE=A.JRNL_HDR_STATUS AND A21X.EFF_STATUS = 'A' AND A21X.EFFDT =
(SELECT MAX(EFFDT) FROM PSXLATITEM TB WHERE TB.FIELDNAME=A21X.FIELDNAME AND TB.FIELDVALUE=A21X.FIELDVALUE AND TB.EFF_STATUS = 'A' AND TB.EFFDT <=
TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') ) LEFT OUTER JOIN PSXLATITEM A61X ON A61X.FIELDNAME='SYSTEM_SOURCE' AND A61X.FIELDVALUE=A.SYSTEM_SOURCE AND A61X.EFF_STATUS = 'A' AND A61X.EFFDT =
(SELECT MAX(EFFDT) FROM PSXLATITEM TB WHERE TB.FIELDNAME=A61X.FIELDNAME AND TB.FIELDVALUE=A61X.FIELDVALUE AND TB.EFF_STATUS = 'A' AND TB.EFFDT <=
TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') ), PS_JRNL_LN B, PS_GL_ACCOUNT_TBL C, PS_OPEN_ITEM_GL D
WHERE ( B.BUSINESS_UNIT = A.BUSINESS_UNIT
AND B.JOURNAL_ID = A.JOURNAL_ID
AND B.JOURNAL_DATE = A.JOURNAL_DATE
AND B.UNPOST_SEQ = A.UNPOST_SEQ
AND ( A.BUSINESS_UNIT_IU = :1
AND A.JOURNAL_ID = :2
AND A.JOURNAL_DATE = TO_DATE(:3,'YYYY-MM-DD')
AND C.SETID = 'SHARE'
AND C.ACCOUNT = B.ACCOUNT
AND C.EFFDT =
(SELECT MAX(C_ED.EFFDT) FROM PS_GL_ACCOUNT_TBL C_ED
WHERE C.SETID = C_ED.SETID
AND C.ACCOUNT = C_ED.ACCOUNT
AND C_ED.EFFDT <= A.JOURNAL_DATE)
AND B.BUSINESS_UNIT = D.BUSINESS_UNIT (+) AND B.JOURNAL_ID =
D.JOURNAL_ID (+) AND B.JOURNAL_DATE = D.JOURNAL_DATE (+) AND B.UNPOST_SEQ
= D.UNPOST_SEQ (+) AND B.JOURNAL_LINE = D.JOURNAL_LINE (+) AND B.LEDGER =
D.LEDGER (+) and 1 = '1' ))
ORDER BY 1, 3, 4, 48, 44, 24

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