Extension Package

 
Applies to:  Customer Mode
 
Inclusions:  Customer section – Version 3.0.68 onwards
Customer Profile Amount section – Version 3.2.79 onwards
Site Use Profile Amount section – Version 3.2.79 onwards
 
Purpose of the Extension Package
 
Customer Wizard has an extension package (M4APS_CW_EXTENSION) that you can modify to meet your requirements. You can use the extension package to perform additional validation during the upload and return messages to the Wizard.
 
The extension package provides access to the uploaded data BEFORE it is inserted into the database tables.
 
The following shows the Wizard upload process when the extension package is used:
 
 
The extension package is called during Upload, NOT during validation.  
 
All messages from the extension package are returned to the relevant sections message column.
 
Messages returned from the extension package to the Wizard will be prefixed with ‘Extension Error:’ to differentiate from the standard 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: CW 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 form:
 
 
 Installing the Extension Package
 
Contact More4apps to request the extension package install files.
 
Included in the cw_install.zip file are the files (cw_ext_install.sql, M4APS_CW_EXTENSION.pkb and M4APS_CW_EXTENSION.pks).  Install the extension package using cw_ext_install.sql.  Make the necessary changes to the procedure.  Compile the package into a test instance and test your code updates thoroughly before compiling it into your production instance.
 
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 five procedures:
·         VERSION
o   VERSION supplies the version number of the extension package and is for More4apps internal use only and MUST NOT be modified.
·         extension_create_cust_account – detailed below
·         extension_update_cust_account - detailed below
·         extension_create_cust_pro_amt - detailed below
·         extension_update_cust_pro_amt - detailed below
 
The procedures prefixed with extension provide access to the customer and profile amount sections (Customer and Site Use) and are the ones you will modify to suit your requirements:
·         Procedure: M4APS_CW_EXTENSION. extension_create_cust_account
 
Parameters
 Name
Mode
Type
Description
p_cust_account_rec_type
IN OUT
HZ_CUST_ACCOUNT_V2PUB.
CUST_ACCOUNT_REC_TYPE
Allows access to the Oracle Public API for customer account creation and parameters contained within.
p_person_rec_type
IN OUT
HZ_PARTY_V2PUB.PERSON_REC_TYPE
Allows access to the Oracle Public API for party person creation and parameters contained within (note the Party creation is a background process).
p_organization_rec_type
IN OUT
HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE
Allows access to the Oracle Public API for party organization creation and parameters contained within (note the Party creation is a background process).
p_cust_profile_rec_type
IN OUT
HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE
Allows access to the Oracle Public API for customer profile creation and parameters contained within (note the Profile creation is a background process).
p_create_profile_amount
IN
VARCHAR2
Allows access to the Oracle Public API for customer and site profile amount creation and parameters contained within (note the Profile creation is a background process).
x_status
OUT
VARCHAR2
Set to 'E' to error the record
x_msg
OUT
VARCHAR2
Used to return a message to the Wizard.
 
·         Procedure: M4APS_CW_EXTENSION. extension_update_cust_account
Parameters
Name
Mode
Type
Description
p_cust_account_rec_type
IN OUT
HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE
Allows access to the Oracle Public API for customer account updates and parameters contained within.
x_status
OUT
VARCHAR2
Set to 'E' to error the record.
x_msg
OUT
VARCHAR2
Used to return a message to the Wizard.
 
·         Procedure: M4APS_CW_EXTENSION. extension_create_cust_pro_amt
Parameters
Name
Mode
Type
Description
p_cust_profile_amt_rec_type
IN OUT
hz_customer_profile_v2pub.cust_profile_amt_rec_type
Allows access to the Oracle Public API for customer account and site profile amount creation and parameters contained within.
x_status
OUT
VARCHAR2
Set to 'E' to error the record.
x_msg
OUT
VARCHAR2
Used to return a message to the Wizard.
 
·         Procedure: M4APS_CW_EXTENSION. extension_update_cust_pro_amt
Parameters
Name
Mode
Type
Description
p_cust_profile_amt_rec_type
IN OUT
hz_customer_profile_v2pub.
cust_profile_amt_rec_type
Allows access to the Oracle Public API for customer account and site profile amount updates and parameters contained within.
x_status
OUT
VARCHAR2
Set to 'E' to error the record.
x_msg
OUT
VARCHAR2
Used to return a message to the Wizard.
 
Using the Extension Package for Additional Validation
 
This example:
Conditionally set a new customer number with error handling
declare
       
           --Custom Variables
           l_vu_custnum    hz_cust_accounts.account_number%type;
           l_vu_custcount  number :=0;
           l_resp_id        number;
           --End of Custom Variables
       
        begin
       
           if p_cust_account_rec_type.account_number = 'XXXXX' and
              fnd_global.resp_id =  56958   then --Vision Utilities
             
              select 'VU'||lpad(m4a_cw_custnum_s.nextval,5,0)
              into   l_vu_custnum
              from   dual;
             
             /* Now check not creating duplicate Account Number */
              begin
                select count(*)
                into   l_vu_custcount
                from   hz_cust_accounts
                where  account_number = l_vu_custnum;
               
                if l_vu_custcount <> 0 then
                   x_status := 'E';
                   x_msg := 'VU Customer Number '||l_vu_custnum||' already exists; please contact your System Administrator';
                else
                   p_cust_account_rec_type.account_number := l_vu_custnum;
                   --x_msg := 'New Cust Num: '||l_vu_custnum; --Extension Error: New Cust Num: VU00004
                end if;
              end;
           end if; --Vision Australia
       
        exception
           when others then
                x_status := 'E';
                x_msg := 'Unable to derive Vision Uni Customer Number; please contact your System Administrator';
       
        end;
 
Using the Extension Package to Update a Profile Amount
 
This example checks for the profile amount attribute1 value, if it is a match the record is updated, if not an error is returned.
DECLARE   
          l_min_dunning_amount   NUMBER;        
        BEGIN
             IF   p_cust_profile_amt_rec_type.attribute1 = 'Download Test CC1'        THEN
                l_min_dunning_amount :=50;
                p_cust_profile_amt_rec_type.min_dunning_amount := l_min_dunning_amount;
            ELSE
                x_status := 'E';
                x_msg := 'FAIL';
            END IF;    
        END;
 
Note - Further examples can be found within the body of the Extension Package.
 
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/24', 'yyyy/mm/dd');
   p_date:=  TO_DATE('23-AOU-2024', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=FRENCH');
   p_date:=  ('23-AUG-2024', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN');
 
Incorrect usage:
   p_date:=  ('23-AUG-2024');
 
Testing and Rollout
 
Compile the package into a test instance and test your code updates thoroughly before compiling it into your production instance.