Assignment 7-9: Creating a Package for Pledges
Create a package named PLEDGE_PKG that includes two functions for determining dates of
pledge payments. Use or create the functions described in Chapter 6 for Assignments 6-12 and
6-13, using the names DD_PAYDATE1_PF and DD_PAYEND_PF for these packaged functions.
Test both functions with a specific pledge ID, using an anonymous block. Then test both
functions in a single query showing all pledges and associated payment dates. (Corresponding source code for functions DD_PAYDATE1_SF and DD_PAYEND_SF is provided in the attached file DD_PAY.sql.)
Assignment 7-10: Adding a Pledge Display Procedure to the Package
Modify the package created in Assignment 7-9 as follows:
• Add a procedure named DD_PLIST_PP that displays the donor name and all
associated pledges (including pledge ID, first payment due date, and last payment
due date). A donor ID is the input value for the procedure.
• Make the procedure public and the two functions private.
Test the procedure with an anonymous block.
Assignment 7-11: Adding a Payment Retrieval Procedure to the Package
Modify the package created in Assignment 7-10 as follows:
• Add a new procedure named DD_PAYS_PP that retrieves donor pledge payment
information and returns all the required data via a single parameter.
• A donor ID is the input for the procedure.
• The procedure should retrieve the donor’s last name and each pledge payment
made so far (including payment amount and payment date).
• Make the procedure public.
Test the procedure with an anonymous block. The procedure call must handle the data
being returned by means of a single parameter in the procedure. For each pledge payment,
make sure the pledge ID, donor’s last name, pledge payment amount, and pledge payment
date are displayed.
Assignment 9-9: Tracking Pledge Payment Activity
The DoGood Donor organization wants to track all pledge payment activity. Each time a pledge
payment is added, changed, or removed, the following information should be captured in a
separate table: username (logon), current date, action taken (INSERT, UPDATE, or DELETE),
and the idpay value for the payment record. Create a table named DD_PAYTRACK to hold
this information. Include a primary key column to be populated by a sequence, and create a
new sequence named DD_PTRACK_SEQ for the primary key column. Create a single trigger for
recording the requested information to track pledge payment activity, and test the trigger.