The Extension Package applies to the following:
· Standard Purchase Order and Change API Modes.
· Purchase Order Wizard Version 4.8.12 onwards
· Purchase Order Wizard Database Package Version onwards:
o Header Version 2.0.00 07 Nov 2023
o Body Version 2.0.01 27 Feb 2024
Exclusions:
· Change API Mode
o Descriptive Flex Fields (DFFs) are not supported. This is because they are profile option controlled and not supported by the Oracle API function (PO_CHANGE_API1_S.update_po)
Purpose of the Extension Package
Extension packages allow customers to implement their own custom business logic. They can be used to perform additional validation on the uploaded data, override values on the uploaded data and return messages to the Wizard.
The Extension Package provides access to the uploaded data BEFORE it is inserted into the interface tables or passed to the API.
The following diagram shows the Wizard upload process when the Extension Package is used:
The Extension Package is called during Upload NOT during validation.
Messages returned from the extension package to the Wizard will be prefixed with ‘Extension Error:’ to differentiate from the standard Purchase Order Wizard validation and validation returned from the Oracle Public APIs.
Enabling the Extension Package
The use of the Extension Package is controlled by the profile option ‘More4Apps: POW enable Extension Pkg’. The profile is turned off by default. When set to ‘Yes’ it allows the customer to utilize the extension package. The profile enables the extension package for all modes (that support extension package functionality). If the profile does not exist it will be created by the Wizard on first login.
A user can determine whether the extension package is enabled for them by viewing the About This Program form on the Wizard ribbon:
Installing the Extension Package
The extension package is included in the pow_install.zip file. The extension package will be installed when running the server side install.
The extension package functionality was added to Purchase Order Wizard Version 4.8.12. To utilize this functionality, you must install the server-side files included with this version or later versions of the Wizard.
Caveat
Any extra code has the potential to impact the performance of the upload. In extreme cases this could result in timeouts and no data being returned to the wizard. Performance testing is advised when implementing your code in the extension package.
Support
More4apps does not support customer code. If an upload issue occurs, More4apps will require the issue to be replicated with the profile option set to “No”. If the issue occurs with the profile set to “Yes” and does not occur with the profile option set to “No” then it is your responsibility to fix the issue.
If the extension package is being used to modify the uploaded data, it is recommended that a message is written back to the Wizard indicating that the data has been modified by the extension package - this will help diagnose any upload issues.
Technical Details
Modifying the extension package will require technical knowledge of PL/SQL (i.e. a developer or DBA resource) as well as functional knowledge to determine the logic.
The security context will be set for the database session. Hence context values like responsibility and user can be obtained from the session.
Extension Package Procedures
The extension package includes three procedures: version, stdpo and changeapi.
- The version procedure supplies the version number of the extension package and is for More4apps internal use only and MUST NOT be modified.
- The procedure stdpo provides access to data uploaded through the ‘Standard Purchase Order’ mode. This is the procedure you modify to suit your requirements for the ‘Standard Purchase Order’ mode.
- The procedure changeapi provides access to data uploaded through the ‘Change API’ mode. This is the procedure you modify to suit your requirements for the ‘Change API’ mode.
Security Context
The database session will have the EBS security context set. These context sensitive environment variables can be accessed for reading but MUST not be changed. Changing any of these values could have serious unintended consequences on the operation of the Wizard.
Implicit Conversions and Language Considerations
Do not rely on implicit data type conversions and do not hard code language dependent values.
When implicit conversions occur, or language dependent values are hardcoded it can be very difficult to analyze issues because the code will often run without issues through SQL Developer but fail when run from the Wizard.
The NLS settings in which the Wizard runs are not fixed and can change depending on the session.
Date Formatting as an Example
When hard coding date values always use TO_DATE to explicitly state the date format.
Without explicitly stating the format, you are likely to see this message returned to the Wizard "SQL Error:com.more4apps.r12.servlet.tjava.sql.SQLDataException: ORA-01858: a non-numeric character was found where a numeric was expected".
Correct usage:
|
p_date:= TO_DATE('2015/08/23', 'yyyy/mm/dd');
p_date:= TO_DATE('23-AOU-2015', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=FRENCH');
p_date:= ('23-AUG-2015', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN');
|
Incorrect usage:
|
p_date:= ('23-AUG-2015');
|
Refer to section:
