Extension Package

 
Applies to:
 
●    Requisition and iProcurement Modes.
●    Requisition Wizard Version 9.1.07 onwards.
●    Requisition Wizard Database Package Versions onwards:
○        Header Version 5.1.00 23 Jun 2021
○        Body Version 5.1.00 23 Jun 2021
 
Exclusions:
 
●    Does not support the deprecated SPIT functionality.
 
Purpose of the Extension Package
Requisition Wizard has an extension package (M4APS_RW_EXTENSION) that you can modify to meet your requirements. You can use the extension package 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.
 
The following shows the Wizard upload process when the extension package is used:
 
 
The extension package is called during Upload, NOT during validation.  The extension package is called once per uploaded requisition.
 
Extension package validation errors encountered in any section of the requisition will result in the entire requisition being rejected. For example, just 1 distribution line error will reject the entire requisition.
 
All messages from the extension package are returned to the HEADER SECTION’s message column.  Messages CANNOT be returned to any of the child section’s message columns.  However, you can include row numbers and section names in your message eg Extension Error: Forbidden Charge Account Distributions: Rows, 15,16.
 
Messages returned from the extension package to the Wizard will be prefixed with ‘Extension Error:’ to differentiate from the standard Requisition Wizard validation and validation returned from the Oracle Public APIs.
 
Example of the extension package rejecting the requisition upload as per the guidelines stated above:
 
 
Enabling the Extension Package
The use of the extension package is controlled by the profile option ‘More4Apps: RW 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 rw_install.zip file.  The extension package will be installed when running the server side install.
 
The extension package functionality was added to Requisition Wizard Version 9.1.07.  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 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.
 
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.
 
Extension Package Procedures
The extension package includes two procedures: VERSION and REQUISITION.
VERSION supplies the version number of the extension package and is for More4apps internal use only and MUST NOT be modified.
 
The procedure REQUISITION provides access to the uploaded data and is the one you will modify to suit your requirements.
 
The uploaded data parameters match the structure of the requisition interface tables (po_requisitions_interface and po_req_dist_interface).  NB The requisition interface tables only include line and distribution level tables.  There is no header table for this interface.  Header data is stored against each requisition line i.e the header data is duplicated across lines for the same requisition.
 
Procedure: M4APS_RW_EXTENSION.REQUISITION
Parameters
Name
Mode
Type
Description
t_line_row
IN
m4aps_reqwizard.req_lines_row_tab
t_line_row(x) indicates the spreadsheet row for that line.
t_line_row and p_req_lines share the same binary index values. t_line_row holds a reference to the row in the wizard the line belongs to.
p_req_lines
IN OUT
m4aps_reqwizard.req_lines_tab
table of line data to be inserted into the interface table.
t_dist_row
IN
m4aps_reqwizard.req_dist_row_tab
t_dist_row(x).l_line links to line record
t_dist_row(x).l_row indicates the spreadsheet row for that dist
t_dist_row and p_req_dists share the same binary index values. t_dist_row(x).l_row holds a reference to the row in the wizard the distribution belongs to.
 
t_dist_row(x).l_line holds the binary index value for the distributions associated line in the p_req_lines table.
 
p_req_dists
IN OUT
m4aps_reqwizard.req_dist_tab
table of Distribution data to be inserted into interface table
x_status
OUT
VARCHAR2
set to 'E' to error the requisition and stop it from being inserted into the interface table
x_message
OUT
VARCHAR2
Used to return a message to the wizard. Messages are returned to the header record in the wizard.
 
Relevant m4aps_reqwizard objects
 
Name
Definition
req_lines_row_tab
TYPE req_lines_row_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
req_lines_tab
TYPE req_lines_tab IS TABLE OF po_requisitions_interface%ROWTYPE;
req_dist_row_tab
TYPE req_dist_row_rec IS RECORD (
   l_row   NUMBER,
   l_line  NUMBER
   );
TYPE req_dist_row_tab IS TABLE OF req_dist_row_rec INDEX BY BINARY_INTEGER;
req_dist_tab
TYPE req_dist_tab IS TABLE OF po_req_dist_interface%ROWTYPE;
 
Using the Extension Package for Additional Validation
This example:
●    Rejects a requisition if any of its distributions have a NULL value for DISTRIBUTION_ATTRIBUTE1.
●    Returns a message to the wizard detailing the row number of all distributions with a NULL value for DISTRIBUTION_ATTRIBUTE1. This message is printed to the header section in the Wizard.
 
DECLARE
  l_cntr NUMBER;
BEGIN
  FOR l_cntr IN 1..p_req_dists.count
    LOOP
    IF p_req_dists(l_cntr).DISTRIBUTION_ATTRIBUTE1 IS NULL THEN
      x_status := 'E';
      IF x_message IS NULL THEN
        x_message := 'Distribution Attribute1 is required. Row ' || t_dist_row(l_cntr).l_row;
      ELSE
        x_message := x_message || ',' || t_dist_row(l_cntr).l_row;
      END IF;
    END IF;
  END LOOP;
END;
 
Using the Extension Package to Update a Header Value
This example updates the HEADER_ATTRIBUTE1 value. Because the header values are stored in a de-normalised form within p_req_lines all records must be updated with the new header value. If you do not update all lines the requisition imports grouping rules will create a new requisition for each line that has different header values.
 
DECLARE
  l_cntr NUMBER;
BEGIN
  FOR l_cntr IN 1..p_req_lines.count
  LOOP
    p_req_lines(l_cntr).HEADER_ATTRIBUTE1 := 'HEADER_ATTRIBUTE1';
  END LOOP;  
END;
 
Processing Distributions for Each Line
This example processes distributions for each line.
 
Business Rule: If a line has a line type of ‘Goods’ return a message back to the wizard when a distribution does not have a value for Distribution Attribute1.  This message includes the row number of the line and the distributions.  This message is printed to the header section in the Wizard.
 
DECLARE
  l_cntr NUMBER;
  l_cntr_d NUMBER;
BEGIN
  FOR l_cntr IN 1..p_req_lines.count
  LOOP
    IF p_req_lines(l_cntr).l_req_lines_rt.line_type_id = 1 THEN --Goods
      FOR l_cntr_d IN 1..p_req_dists.count
      LOOP
        IF t_dist_row(l_cntr_d).l_line = l_cntr THEN
          IF p_req_dists(l_cntr).distribution_attribute1 IS NULL THEN
            x_status := 'E';
            IF x_message IS NULL THEN
              x_message := 'Goods lines are normally expected to have distributions with values for Attribute1: Line Row ' || t_line_row(l_cntr) || ' - Dist Row ' || t_dist_row(l_cntr_d).l_row;
            ELSE
              x_message := x_message || ', Line Row ' || t_line_row(l_cntr) || ' - Dist Row ' || t_dist_row(l_cntr_d).l_row;
            END IF;
        END IF;
      END LOOP;
    END IF;
  END LOOP;  
END;
 
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 hard coded 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.