Extension Package

 
The Extension Package applies to the following:
 
•     Bill of Materials Wizard Version 7.2.00 onwards.
 
•     All Modes.
 
•     Bill of Materials Wizard Database Package Versions onwards:
 
Header Version 2.2.00   29 Sep 2021
Body Version 1.8.00      30 Sep 2021
 
•     Bill of Materials Wizard Extension Package Versions onwards:
 
Header Version 1.1.0    30 Sep 2021
Body Version 1.1.0       30 Sep 2021
 
Purpose of the Extension Package
 
Bill of Materials Wizard has an extension package (M4APS_BMW_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 API.
 
The following diagram shows the Wizard upload process when the Extension Package is used:
 
 
 
The extension package is called during Upload after Wizard validation. The extension package is called once per uploaded Bill of Materials (BOM).
 
Extension package validation errors encountered in any section of the Bill of Material will result in the entire Bill of Material being rejected.  For example, just one child/grandchild error will reject the entire Bill of Material like a normal upload.
 
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 e.g. ‘Not required for revenue: Row(s) 12,13,14’.
 
Messages returned from the Extension Package to the Wizard will be prefixed with ‘Extension Error:’ to differentiate from the Standard Bill of Materials Wizard validation and validation messages returned from the Oracle Public APIs.
 
Example of the Extension Package rejecting the Bill of Material upload as per the guidelines stated above:
 
 
Enabling the Extension Package
 
The use of the Extension Package is controlled by the profile option ‘More4Apps: BMW 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 login.
Refer to section:  Profile Options
 
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 bmw_install.zip file.  The extension package will be installed when running the server-side install.
 
The extension package functionality was added to Bill of Materials Wizard version 7.2.00.  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 (ie 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 BOM.
- VERSION supplies the version number of the extension package and is more More4apps internal use only and MUST NOT be modified.
- BOM 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 BOM API.
Procedure: M4APS_BMW_EXTENSION.extend_bom
 
Parameters
 
Name
Mode
Type
Description
Bom_hdr_tbl
IN OUT
bom_bo_pub.bom_head_rec_ type
Record of Header data to be inserted into the API. As the package runs for each header this is a single row
Bom_rev_tbl
IN OUT
bom_bo_pub.revision_tbl
Table of revision data to be inserted into the API. populated 1 to 1 with the header so will be a single row.
Bom_comp_tbl
IN OUT
bom_bo_pub.bom_comps_tbl
Table of component data to be inserted into the API.
Bom_sub_comp_tbl
IN OUT
bom_bo_pub.bom_sub_component_ tbl
Table of substitute component data to be inserted into the API.
bom_comp_ops_tbl
IN OUT
bom_bo_pub.bom_comp_ops_tbl
table of assigned operation data to be inserted into the API.
bom_ref_des_tbl
IN OUT
bom_bo_pub.bom_ref_designator_tbl
Table of reference designator data to be inserted into the API.
t_bom_component_row
IN
m4aps_bomwizard.bom_component_row_tab
t_bom_component_row(x) indicates the spreadsheet row for that component.
t_bom_sub_component_row
IN
m4aps_bomwizard.bom_sub_ component_row_ tab
t_bom_sub_component_row(x).l_row indicates the spreadsheet row for that substitute component.
t_bom_sub_component_row(x).l_component links to the component data.
t_bom_comp_ops_row
IN
m4aps_bomwizard.bom_comp_ops_ row_tab
t_bom_comp_ops_row(x).l_row indicates the spreadsheet row for that assigned operation.
t_bom_comp_ops_row(x).l_component links to the component data.
t_bom_ref_des_comp_row
IN
m4aps_bomwizard.bom_ref_des_ comp_row_tab
t_bom_ref_des_comp_row(x).l_row indicates the spreadsheet row for that reference designator.
t_bom_ref_des_comp_row(x).l_component links to the component data.
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_bomwizard objects:
 
Name
Definition
bom_component_row_tab
 TYPE bom_component_row_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
bom_sub_component_row_tab
TYPE bom_sub_component_row_rec IS RECORD (
l_row       NUMBER
, l_component  NUMBER
);
TYPE bom_sub_component_row_tab IS
TABLE OF bom_sub_component_row_rec INDEX BY BINARY_INTEGER;
bom_comp_ops_row_tab
TYPE bom_comp_ops_row_rec IS RECORD (
l_row       NUMBER
, l_component  NUMBER
);
TYPE bom_comp_ops_row_tab IS
TABLE OF bom_comp_ops_row_rec INDEX BY BINARY_INTEGER;
bom_ref_des_comp_row_tab
  TYPE bom_ref_des_comp_row_rec IS RECORD (
      l_row       NUMBER
      , l_component  NUMBER
  );
  TYPE bom_ref_des_comp_row_tab IS
      TABLE OF bom_ref_des_comp_row_rec INDEX BY BINARY_INTEGER;
 
Using the Extension Package for Additional Validation
 
This example:
 
·         Rejects a Bill of Materials if it is in a particular Organization.
·         Returns a message to the Wizard explaining that no Bill of Materials belonging to that organization can be loaded.  This message is printed in the Header section in the Wizard.
 
 IF Bom_Hdr_Tbl.organization_code  = 'M2' THEN
   x_status := 'E';
   x_message := 'You are NOT allowed to load BOMs for the M2 organization.';   
 End If;
 
Using the Extension Package to set the cost_rollup to include only optional Components
 
This example updates the include_in_cost_rollup to ‘Yes’ for all optional components and ‘No’ for non-optional components.
 
 DECLARE
   l_cntr NUMBER;
 BEGIN   
   FOR l_cntr IN 1..bom_comp_tbl.count
   LOOP
  IF bom_comp_tbl(l_cntr).optional = 1 THEN
       bom_comp_tbl(l_cntr).include_in_cost_rollup:=1;
  ELSE
         bom_comp_tbl(l_cntr).include_in_cost_rollup:=2;
  END IF;
   END LOOP;
 END;
 
Processing Substitute Component
 
This example:
 
·         Finds where an obsolete item is used as a substitute component.
 
·         Returns a message to the Wizard explaining where the obsolete item exists.  This message is printed to the header section in the Wizard.
 
   DECLARE
  l_cntr NUMBER;
   BEGIN
  FOR l_cntr IN 1..bom_sub_comp_tbl.count LOOP
          IF bom_sub_comp_tbl(l_cntr).substitute_component_name = 'SAM001' THEN
          x_status := 'E';
          IF x_message IS NULL THEN
            x_message :=‘SAM001 is obsolete.Row'||t_sub_component_row(l_cntr).l_row;
          ELSE
            x_message := x_message
                       || ', '
                       || t_sub_component_row(l_cntr).l_row;
          END IF;
          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 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.