Tips on Writing and Debugging Procedures
You can make testing and debugging your billing extension procedure much easier by writing your procedure in a very methodical, structured approach as suggested below. Your functional and technical resources should work together to validate the billing extension.
Step 1 Create own billing extension to create event of a given amount
The first step is to create a very simple billing extension using the template files. You perform these steps to create an automatic event using a billing extension.
- Copy the template files to your own files
- Change the package and procedure names
- Add one call to the insert_event procedure to create an event of a given amount
- Store the procedure in the database
- Define a billing extension in Oracle Projects using this procedure
- Assign the billing extension to a test project
- Process the project through revenue and invoice generation; you should run the process that is appropriate for the billing extension
- Verify that an event is created for the given amount
Step 2 Test each SQL statement in SQL*Plus
After you verify that your billing extension works in an integrated flow, you can begin to build the logic of your billing extension. You first write and test each SQL statement in SQL*Plus. You focus on each SQL statement independently until you have verified all of the SQL statements.
Note: Be sure that the appropriate SQL statements handle both project level and top task level billing assignments.
If you are writing transaction dependent billing extensions, you should create the appropriate transactions on your test project and then process the transactions through revenue accrual or invoicing. Note the request ID of the process. All of the transactions are marked with this request ID, so you can use the request ID in testing your SQL statements in SQL*Plus. You can then use one of the following views to read the appropriate transactions processed by the request ID.
- PA_BILLING_REV_TRANSACTIONS_V
- PA_BILLING_INV_TRANSACTIONS_V
The views use PL/SQL functions, which are included in the view definition, to determine the appropriate project, task, calling place, and request ID variables for which the billing extension is being run. These variables are set by the revenue generation and invoice generation processes before the billing extension is executed. If you do not set these variables, then the view returns all records for that project and task in SQL*Plus. You can set these variables for your SQL*Plus session by running the papbglob.sql script which exists in the admin/sql directory. You can test your SQL statements using views with the variables that you want.
Step 3 Add SQL statements one at a time and test in an integrated flow
After you test and verify each SQL statement that you plan to use in your billing extension, you can add one SQL statement at a time to your billing extension definition. Each time after you add a new part of the logic to the billing extension, you should then test your billing extension in an integrated revenue or invoice flow through Oracle Projects to verify the logic that you just added. Continue this cycle for all of your SQL statements to be included in your billing extension procedure.
You may take another approach by adding all of your logic to the billing extension and then performing integrated testing. This method is harder to debug when you encounter problems.
Step 4 Do full integrated testing of billing extension
After your billing extension logic is complete, you need to perform full integrated testing to validate all of the business cases and conditions that your billing extension must handle. This is where you use the business cases and test plans that you created in the design stage of the your billing extension implementation.
You must ensure that your billing extension works when using both project level and task level funding, if your company uses both levels of funding.
If you have written a transaction dependent billing extension, you should test the processing flow for these adjustment actions to ensure that your billing extension properly processes transactions with these adjustment actions:
- Revenue recalculation with and without change in the amount
- Transfer to the same project, which results in the same amount
- Transfer to a different task, which results in a different amount
- Transfer to a different project
- Billable to non-billable reclassification
Once you have verified all of the integrated test cases, you have completed your billing extension implementation.
Other Debugging Tips
- Make sure that the name seeded in pa_billing_ extensions.procedure_name is exactly the same as the package.procedure_name if your procedure is stored in the database
- Make sure that the package.procedure_name does not exceed 30 characters
- Make sure that your procedure is compiled and stored in the database
- Make sure that there is not another invalid or outdated procedure executing instead of the procedure you intend to execute. Inactivate all other extensions at the appropriate level to ensure that only the extension you expect to execute is executing.
