PO Output For Communication Program Fails java.sql.SQLException: ORA-01722: Invalid Number (Doc ID 1305981.1)

Last updated on AUGUST 11, 2022

Applies to:

Symptoms

Execution of the PO Output for Communication process completes with errors.

Error:
m_withTerms : Y
Executing the query for header id
Query for header id :: SELECT decode(:1 , 'PO NUMBER', null, HRE.last_name), decode( :2 , 'PO NUMBER', null, HRE.first_name), decode( :3 ,'NUMERIC', null, poh.segment1), decode(:4 ,'NUMERIC', decode(rtrim(poh.segment1,'0123456789'),NULL,to_number(poh.segment1),-1), null), po_header_id, nvl(revision_num,0) revision_num, type_lookup_code, NVL(authorization_status,'INCOMPLETE') authorization_status, nvl(user_hold_flag,'N') user_hold_flag, nvl(consigned_consumption_flag,'N') consigned_consumption_flag ,'PO' doc_identifier, poh.vendor_id,poh.vendor_site_id FROM po_headers_all poh, PER_ALL_PEOPLE_F HRE WHERE poh.agent_id = nvl (:5 , poh.agent_id) And Nvl (poh.approved_flag,'N') = nvl (:6 , nvl (poh.approved_flag,'N')) AND (TO_NUMBER(poh.segment1) >= nvl(:7, TO_NUMBER(poh.segment1)) AND TO_NUMBER(poh.segment1) <= nvl(:8, TO_NUMBER(poh.segment1))) AND HRE.PERSON_ID = POH.AGENT_ID AND HRE.EMPLOYEE_NUMBER IS NOT NULL AND TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE AND HRE.EFFECTIVE_END_DATE AND nvl(poh.org_id,-99) = nvl(to_number(RTRIM(SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99) AND poh.type_lookup_code in ('STANDARD','CONTRACT','BLANKET')UNION ALL SELECT distinct decode(:1 , 'PO NUMBER', null, HRE.last_name), decode( :2 , 'PO NUMBER', null, HRE.first_name), decode( :3 ,'NUMERIC', null, poh.segment1), decode(:4 ,'NUMERIC', decode(rtrim(poh.segment1,'0123456789'),NULL,to_number(poh.segment1),-1), null), poh.po_header_id, nvl(poh.revision_num,0) revision_num, type_lookup_code,nvl(poh.authorization_status,'INCOMPLETE') authorization_status , nvl(poh.user_hold_flag,'N') user_hold_flag, nvl(poh.consigned_consumption_flag,'N') consigned_consumption_flag ,'REL' doc_identifier, poh.vendor_id,poh.vendor_site_id FROM po_headers_all poh, po_releases_all por ,PER_ALL_PEOPLE_F HRE WHERE por.po_header_id = poh.po_header_id AND poh.agent_id = nvl (:5 , poh.agent_id) And Nvl (poh.approved_flag,'N') = nvl (:6 , nvl (poh.approved_flag,'N')) AND (TO_NUMBER(poh.segment1) >= nvl(:7, TO_NUMBER(poh.segment1)) AND TO_NUMBER(poh.segment1) <= nvl(:8, TO_NUMBER(poh.segment1))) AND HRE.PERSON_ID = POH.AGENT_ID AND HRE.EMPLOYEE_NUMBER IS NOT NULL AND TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE AND HRE.EFFECTIVE_END_DATE AND nvl(poh.org_id,-99) = nvl(to_number(RTRIM(SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99) AND poh.type_lookup_code ='BLANKET' order by 1,2,3,4,5,11
java.sql.SQLException: ORA-01722: invalid number
java.sql.SQLException: ORA-01722: invalid number
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:590)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1973)
at oracle.jdbc.ttc7.TTC7Protocol.fetch(TTC7Protocol.java:1231)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2616)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2963)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:658)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:584)

Replication Steps:
1. Login to the E-Business Suite and choose the Purchasing Module.
2. Reports/Run - Single Request - PO Output for Communication
3. Enter the parameters, and submit.

The following sql was engaged:
select segment1, ascii (segment1), org_id
from po_headers_all
where translate(segment1
,'01234567890QWERTYUIOPASDFGHJKLZXCVBNMqwertyuiopasdfghjklzxcvbnm'
,'012345678909999999999999999999999999999999999999999999999999999') != segment1;

The org_id (operating unit) where the program was being engaged from was NOT having any non-numeric purchase orders.

Changes

Cause

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