Extension Package

 
Applies to:
 
•       Supplier, Supplier Bank Acc, Site, and Site Bank Acc sections
•       Supplier Wizard version 5.02.83 onwards
•       Supplier Wizard Database Package Versions onwards
o    Header Version 2.1.03 04 Apr 18
o    Body Version 2.1.41 14 Jan 22
 
Exclusions:
 
•       Any sections not listed above
 
Purpose of the Extension Package
 
Supplier Wizard has an extension package (M4APS_SW_EXTENSION) that you can modify to meet your requirements. You can use the extension package to perform additional validation on the uploaded data and override values on the uploaded data.
 
The extension package provides access to the uploaded data BEFORE it is sent to the Oracle API.
 
Only sections with an “Update Mode” value populated will be checked by the extension package e.g. If you are adding a Site to an existing Supplier using “Create” at Site level but leaving the “Supplier Update Mode” blank, only the Site data will be validated by the extension package.
 
The following diagram shows the Wizard upload process when the extension package is used:
 
 
Note:  Extension Package messages will not be returned for successful uploads.
 
Normally, the extension package is called during Upload NOT during validation. However, you can enable “Validate using API” in the “Supplier Wizard - Setup” ribbon tab in order to validate using the Extension package and the Oracle API without uploading.
 
The relevant extension package functions are called for each uploaded supported section record.
 
Messages returned from the extension package to the Wizard will include the term ‘Extension Error:’ after the API name and before your custom error message. This helps to differentiate from the standard Supplier Wizard validation and validation returned from the Oracle Public APIs.
 
Example of the extension package rejecting the Bank Account (but not the Supplier) upload as per the guidelines stated above:
 
 
Enabling the Extension Package
 
The use of the extension package is controlled by the profile option ‘More4Apps: SW Enable Extension Pkg’.  The profile is turned off by default.  When set to ‘Yes’ it allows the customer to utilize the extension package.  If the profile does not exist it will be created by the Wizard on first logon.
Refer to section:  Profile Options
 
A user can determine whether the extension package is enabled for them by viewing the About form:
 
 
Installing the Extension Package
 
The extension package is included in the sw_install.zip file. The extension package will be installed when running the server side install.
 
The extension package functionality was added to Supplier Wizard version 5.02.83. To utilise 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 cannot 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.
 
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.
 
Extension Wizard Procedures
 
The extension package includes four procedures: VERSION, VENDOR_EXTENSION, VENDOR_SITE_EXTENSION and BANK_ACCT_EXTENSION. 
 
VERSION supplies the version number of the extension package and is for More4apps internal use only and MUST NOT be modified.
 
The other procedures provide access to the uploaded data and are the ones you will modify to suit your requirements.
 
The uploaded data parameters match the structure of the relevant Oracle API record types.
 
Procedure:  M4APS_SW_EXTENSION.VENDOR_EXTENSION
 
Parameters
Name
Mode
Type
Description
vendor_rec
IN OUT
AP_VENDOR_PUB_PKG.R_VENDOR_REC_TYPE
This record type holds the information that will be passed to one of the following Oracle APIs:
 
ap_vendor_pub_pkg.update_vendor
Ap_vendor_pub_pkg.create_vendor
 
You can validate or modify this data within this procedure.
x_status
OUT
VARCHAR2
set to 'E' to error the record and stop it from being uploaded.
x_message
OUT
VARCHAR2
Used to return a message to the wizard. Messages are returned to the rejection reason column in the wizard when x_status is 'E'.
 
Procedure:  M4APS_SW_EXTENSION.VENDOR_SITE_EXTENSION
 
Parameters
Name
Mode
Type
Description
vendor_rec
IN OUT
AP_VENDOR_PUB_PKG.R_VENDOR_SITE_REC_TYPE
This record type holds the information that will be passed to one of the following Oracle APIs:
 
ap_vendor_pub_pkg.update_vendor_site
ap_vendor_pub_pkg.create_vendor_site
 
You can validate or modify this data within this procedure.
x_status
OUT
VARCHAR2
set to 'E' to error the record and stop it from being uploaded.
x_message
OUT
VARCHAR2
Used to return a message to the wizard. Messages are returned to the rejection reason column in the wizard when x_status is 'E'.
 
Procedure: M4APS_SW_EXTENSION.BANK_ACCT_EXTENSION
 
Parameters
Name
Mode
Type
Description
vendor_rec
IN OUT
IBY_EXT_BANKACCT_PUB.ExtBankAcct_rec_type
This record type holds the information that will be passed to one of the following Oracle APIs:
 
iby_ext_bankacct_pub.create_ext_bank_acct
iby_ext_bankacct_pub.update_ext_bank_acct
 
You can validate or modify this data within this procedure.
x_status
OUT
VARCHAR2
set to 'E' to error the record and stop it from being uploaded.
x_message
OUT
VARCHAR2
Used to return a message to the wizard. Messages are returned to the rejection reason column in the wizard when x_status is 'E'.
 
Using the Extension Package for Additional Validation
 
This example:
•       Rejects a Supplier when the Vendor Type is “VENDOR”
•       Returns a message to the Wizard
 
PROCEDURE vendor_extension (
    vendor_rec IN OUT ap_vendor_pub_pkg.r_vendor_rec_type
    , x_status    OUT VARCHAR2
    , x_message   OUT VARCHAR2)IS
BEGIN
    IF
            vendor_rec.vendor_type_lookup_code = 'VENDOR'
    THEN
            x_status := 'E';
            x_message := 'Invalid vendor type: VENDOR';
    END IF;
END vendor_extension;
 
Using the Extension Package to modify a Bank Account Value
 
This example:
•       Sets the account type to “CASH” if no value is uploaded via the Wizard.
•       If an account value is provided in the Wizard and it’s not “CASH”, it will reject the record and return an error message to the Wizard.
 
PROCEDURE bank_acct_extension (
    bank_acc_rec IN OUT iby_ext_bankacct_pub.extbankacct_rec_type
    , x_status    OUT VARCHAR2
    , x_message   OUT VARCHAR2)    IS
BEGIN
    IF bank_acc_rec.acct_type IS NULL
    THEN
            bank_acc_rec.acct_type := 'CASH';
    ELSIF bank_acc_rec.acct_type <> 'CASH' THEN
            x_status := 'E';
            x_message := 'Account type must be CASH';
    END IF;
END bank_acct_extension;
 
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. Currently the Wizard uses a default date format of YYYYMMDD, however this could be subject to change. 
 
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');
 
Testing and Rollout
 
Compile the package into a test instance and test your code updates thoroughly before compiling it into your production instance.