Define Additional PL/SQL
Click the 'Edit PL/SQL' button to open the Database PL/SQL Editor.
The following form will now be available:
This form contains read-only sections and user editable sections where you can add your own PL/SQL Declarations and pre and post API PL/SQL.
Define User PL/SQL Declarations
First define any variables you intend to use in your PL/SQL. These will be dynamically defined by the Wizard during the upload as additional variables for use:
Now type or paste your PL/SQL Variables into the pane using the correct PL/SQL syntax. Multiple variables should be separated by carriage returns.
Define User Pre-API PL/SQL
Use the vertical scroll bar to move to the next editable section 'User Pre-API PL/SQL' and add any pre upload PL/SQL code you require the Wizard to undertake before calling the procedure:
Define User Post-API PL/SQL
Use the vertical scroll bar to move to the next editable section 'User Post-API PL/SQL' and add any post upload PL/SQL code you require the Wizard to undertake after calling the procedure:
When all variables have been added into the left-hand pane click 'Validate' to check the syntax and 'OK' when ready to close this form.
Note - These changes will not be saved to the template definition on the database until the 'Save To Repository' button in the main template form has been clicked.
P_info is an additional pre-configured parameter already available for use by the Wizard. In the pre and post Upload PL/SQL blocks you can return information into this pre-defined variable which will then be appended to the messages in the Header section automatically.
Tip - To add additional pre-validation into the template and have the record rejected before calling the upload API code, add code similar to the example below:
User PL/SQL Declarations
testdate DATE;
Pre-API PL/SQL
testdate := sysdate;
if p_trx_date < testdate then
x_err_msg := 'Transaction date cannot be prior to today';
x_return_status := 'E';
else
Post-API PL/SQL
end If;
Refer to section: