PO_DISPATCH Fails for Contract Purchase Order with More than one Version (Doc ID 1634597.1)

Last updated on JULY 14, 2015

Applies to:

PeopleSoft Enterprise SCM Purchasing - Version 9.1 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms


When attempting to run PO Dispatching (PO_DISPATCH) for Purchase Order Contract with more than one version. There appears to be a problem with SQL.PO_DISP_CNTRCT_AMT_OPEN the following error occurs.



ERROR:
SQL error. Function: SQLExec

Error Position: 0

Return: 8601 - [Microsoft][SQL Server Native Client 10.0][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, , >= or when the subquery is used as an expression.
[Microsoft][SQL Server Native Client 10.0][SQL

Statement: UPDATE PS_POLINEEXT_TAO5 SET AMT_OPEN = ( SELECT CNTRCT_LINE.AMT_LINE_MAX - RLS.AMT_LINE_RELEASED FROM PS_CNTRCT_HDR HDR , PS_CNTRCT_LINE CNTRCT_LINE , PS_CNTRCT_LIN_RLS RLS , PS_CNTRCT_BPO_XREF BPO WHERE PS_POLINEEXT_TAO5.PROCESS_INSTANCE = :1 AND BPO.BUSINESS_UNIT = PS_POLINEEXT_TAO5.BUSINESS_UNIT AND BPO.PO_ID = PS_POLINEEXT_TAO5.PO_ID AND BPO.SETID = CNTRCT_LINE.SETID AND BPO.CNTRCT_ID = CNTRCT_LINE.CNTRCT_ID AND PS_POLINEEXT_TAO5.CNTRCT_LINE_NBR = CNTRCT_LINE.CNTRCT_LINE_NBR AND HDR.SETID = CNTRCT_LINE.SETID AND HDR.CNTRCT_ID = CNTRCT_LINE.CNTRCT_ID AND HDR.VERSION_STATUS = 'C' AND RLS.SETID = CNTRCT_LINE.SETID AND RLS.CNTRCT_ID = CNTRCT_LINE.CNTRCT_ID AND RLS.CNTRCT_LINE_NBR = CNTRCT_LINE.CNTRCT_LINE_NBR) WHERE EXISTS ( SELECT 'X' FROM PS_CNTRCT_HDR HDR , PS_CNTRCT_LIN_RLS RLS , PS_CNTRCT_BPO_XREF BPO WHERE PS_POLINEEXT_TAO5.PROCESS_INSTANCE = :2 AND BPO.BUSINESS_UNIT = PS_POLINEEXT_TAO5.BUSINESS_UNIT AND BPO.PO_ID = PS_POLINEEXT_TAO5.PO_ID AND HDR.SETID = RLS.SETID AND HDR.CNTRCT_ID = RLS.CNTRCT_ID AND HDR.VERSION_STATUS = 'C' AND BPO.SETID = RLS.SETID AND BPO.CNTRCT_ID = RLS.CNTRCT_ID AND PS_POLINEEXT_TAO5.CNTRCT_LINE_NBR = RLS.CNTRCT_LINE_NBR)

Original Statement: UPDATE %Table(POLINEEXT_TAO) SET AMT_OPEN = ( SELECT CNTRCT_LINE.AMT_LINE_MAX - RLS.AMT_LINE_RELEASED FROM PS_CNTRCT_HDR HDR , PS_CNTRCT_LINE CNTRCT_LINE , PS_CNTRCT_LIN_RLS RLS , PS_CNTRCT_BPO_XREF BPO WHERE %Table(POLINEEXT_TAO).PROCESS_INSTANCE = :1 AND BPO.BUSINESS_UNIT = %Table(POLINEEXT_TAO).BUSINESS_UNIT AND BPO.PO_ID = %Table(POLINEEXT_TAO).PO_ID AND BPO.SETID = CNTRCT_LINE.SETID AND BPO.CNTRCT_ID = CNTRCT_LINE.CNTRCT_ID AND %Table(POLINEEXT_TAO).CNTRCT_LINE_NBR = CNTRCT_LINE.CNTRCT_LINE_NBR AND HDR.SETID = CNTRCT_LINE.SETID AND HDR.CNTRCT_ID = CNTRCT_LINE.CNTRCT_ID AND HDR.VERSION_STATUS = 'C' AND RLS.SETID = CNTRCT_LINE.SETID AND RLS.CNTRCT_ID = CNTRCT_LINE.CNTRCT_ID AND RLS.CNTRCT_LINE_NBR = CNTRCT_LINE.CNTRCT_LINE_NBR) WHERE EXISTS ( SELECT 'X' FROM PS_CNTRCT_HDR HDR , PS_CNTRCT_LIN_RLS RLS , PS_CNTRCT_BPO_XREF BPO WHERE %Table(POLINEEXT_TAO).PROCESS_INSTANCE = :1 AND BPO.BUSINESS_UNIT = %Table(POLINEEXT_TAO).BUSINESS_UNIT AND BPO.PO_ID = %Table(POLINEEXT_TAO).PO_ID AND HDR.SETID = RLS.SETID AND HDR.CNTRCT_ID = RLS.CNTRCT_ID AND HDR.VERSION_STATUS = 'C' AND BPO.SETID = RLS.SETID AND BPO.CNTRCT_ID = RLS.CNTRCT_ID AND %Table(POLINEEXT_TAO).CNTRCT_LINE_NBR = RLS.CNTRCT_LINE_NBR)

SQL error in Exec. (2,280) PO_DISPATCH.PO_Dispatch_Data.OnExecute Name:getContractInfo PCPC:10095 Statement:185

Called from:PO_DISPATCH.PO_Dispatch_Data.OnExecute Name:fillLine Statement:316

Called from:PO_DISPATCH.MAIN.GBL.default.1900-01-01.Step09.OnExecute Statement:3

Process 2528937 ABENDED at Step PO_DISPATCH.MAIN.Step09 (PeopleCode) -- RC = 8 (108,524)

Process %s ABENDED at Step %s.%s.%s (Action %s) -- RC = %s

PSAESRV completed service request at 08.57.41 2014-01-15



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