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.
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.
