When using an Oracle database, the following format requirements are needed when creating stored procedures to retrieve purchase order header data and purchase order line data. These requirements are not necessary when using a different database type, such as SQL Server.

Specifically, the parameter must include the SYS_REFCURSOR parameter type with direction OUT at the end of the parameter list in the stored procedure. Without this, the stored procedure does not work as expected.

The following example shows an example using this syntax.

CREATE OR REPLACE PROCEDURE uspGetPOHeaderData(P0_IN IN VARCHAR2, cursorParam OUT SYS_REFCURSOR)
IS
BEGIN
OPEN cursorParam FOR
SELECT Distinct PO as spPO
      ,VENDORID as spVendor_ID
      ,SITEID as spSite_ID
      ,COMPANYCODE as spCompany_Code
      ,CURRENCY as spCurrency
      ,DOCTYPE as spPO_Type
      ,BUSINESSUNIT as spPlant
FROM BW_POHEADER_DATA
WHERE PO = P0_IN;                        
END;