Advanced users of Application Interface Wizard may want to create their own wrapper packages in the Oracle Database. This may call various API’s, have complex validation and a greater level of control. In this case when setting up the new template you would enter a mode of ‘Procedure’ and enter the custom package and procedure names.
There are some things to be aware of when creating your wrapper package and they are highlighted below.
If you are using your own wrapper packages the following parameters and return values are required in your procedure to return status and messages to Application Interface Wizard:
x_return_status out varchar2
x_err_msg out varchar2
Use this syntax in your procedures to report a successful upload:
x_return_status := FND_API.G_RET_STS_SUCCESS;
Use this syntax in your procedures to report an upload failure with an associated message:
If you have parent/child relationships in your package then each table type must also have these columns to enable statuses and message to be feed back into the Wizard. In addition to this in the main procedure definition the table type parameter must be an in/out return type.
If X_RETURN_STATUS is displayed in the Template the above value of the API constant would be written to this column, for example 'S' or 'E'.
If this column is not displayed on the sheet the above status results are then resolved into Application Interface Wizard syntax and written to the mandatory section status column. For example 'E' would display 'Error' and 'S' would display 'Accepted'
If X_ERR_MSG is displayed in the Template it will contain the same value as the sections 'Messages' column.
