Academic Integrity: tutoring, explanations, and feedback — we don’t complete graded work or submit on a student’s behalf.

Create a package named PLEDGE_PKG that includes two functions for determining da

ID: 3564560 • Letter: C

Question

Create a package named PLEDGE_PKG that includes two functions for determining dates of pledge payments.

First function name is DD_PAYDATE1_PF should determine the first payment date for a pledge based on pledge ID. The first payment is the first day of the month after date the pledge was made. Pledge made in December should reflect first payment date with the following year.

Second function named DD_PAYEND_PF will be the final payment date based on pledge ID. If donation pledge indicates a lump sum payment, the final payment date is the same as the first payment date.

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

TABLES:

DROP TABLE dd_donor CASCADE CONSTRAINTS;
DROP TABLE dd_project CASCADE CONSTRAINTS;
DROP TABLE dd_status CASCADE CONSTRAINTS;
DROP TABLE dd_pledge CASCADE CONSTRAINTS;
DROP TABLE dd_payment CASCADE CONSTRAINTS;
CREATE TABLE DD_Donor (
   idDonor number(4),
   Firstname varchar2(15),
   Lastname varchar2(30),
Typecode CHAR(1),
   Street varchar2(40),
   City varchar2(20),
   State char(2),
   Zip varchar2(9),
   Phone varchar2(10),
   Fax varchar2(10),
   Email varchar2(25),
News char(1),
   dtentered date DEFAULT SYSDATE,
   CONSTRAINT donor_id_pk PRIMARY KEY(idDonor) );
CREATE TABLE DD_Project (
idProj number(6),
Projname varchar2(60),
Projstartdate DATE,
Projenddate DATE,
Projfundgoal number(12,2),
ProjCoord varchar2(20),
CONSTRAINT project_id_pk PRIMARY KEY(idProj),
CONSTRAINT project_name_uk UNIQUE (Projname) );
CREATE TABLE DD_Status (
idStatus number(2),
Statusdesc varchar2(15),
CONSTRAINT status_id_pk PRIMARY KEY(idStatus) );   
CREATE TABLE DD_Pledge (
idPledge number(5),
idDonor number(4),
Pledgedate DATE,
Pledgeamt number(8,2),
idProj number(5),
idStatus number(2),
Writeoff number(8,2),
paymonths number(3),
Campaign number(4),
Firstpledge char(1),
CONSTRAINT pledge_id_pk PRIMARY KEY(idPledge),
CONSTRAINT pledge_idDonor_fk FOREIGN KEY (idDonor)
REFERENCES dd_donor (idDonor),
CONSTRAINT pledge_idProj_fk FOREIGN KEY (idProj)
REFERENCES dd_project (idProj),
CONSTRAINT pledge_idStatus_fk FOREIGN KEY (idStatus)
REFERENCES dd_status (idStatus));   
CREATE TABLE DD_Payment (
idPay number(6),
idPledge number(5),
Payamt number(8,2),
Paydate DATE,
Paymethod char(2),
CONSTRAINT payment_id_pk PRIMARY KEY(idPay),
CONSTRAINT pay_idpledge_fk FOREIGN KEY (idPledge)
REFERENCES dd_pledge (idPledge) );   
INSERT INTO dd_donor
VALUES (301, 'Mary', 'Treanor', 'I','243 main St.', 'Norfolk', 'VA','23510',NULL,NULL,'mtrea492@mdv.com','Y','01-SEP-2012');
INSERT INTO dd_donor
VALUES (302, 'Patrick', 'Lee', 'I','11 Hooper St.', 'Norfolk', 'VA','23510','7572115445',NULL,'pleeNorf@gmail.com','N','09-SEP-2012');
INSERT INTO dd_donor
VALUES (303, 'Terry', 'Venor', 'I','556 Loop Lane.', 'Chesapeake', 'VA','23320',NULL,NULL,'tervenr@drw.edu','Y','18-SEP-2012');
INSERT INTO dd_donor
VALUES (304, 'Sherry', 'Pane', 'I','Center Blvd.', 'Virginia Beach', 'VA','23455',NULL,NULL,'toppane@yahoo.com','Y','21-SEP-2012');
INSERT INTO dd_donor
VALUES (305, 'Thomas', 'Sheer', 'I','66 Train St.', 'Chesapeake', 'VA','23322','7579390022',NULL,'tls3488@sheer.com','Y','01-MAR-2013');
INSERT INTO dd_donor
VALUES (306, NULL, 'Coastal Developers', 'B','3667 Shore Dr.', 'Virginia Beach', 'VA','23450','8889220004',NULL,'coastVA@cdev.com','Y','30-SEP-2012');
INSERT INTO dd_donor
VALUES (307, NULL, 'VA Community Org', 'G','689 Bush Dr.', 'Norfolk', 'VA','23513','7578337467','7578337468','vacmorg@biz.com','Y','03-OCT-2012');
INSERT INTO dd_donor
VALUES (308, 'Betty', 'Konklin', 'I','11 Shark Ln.', 'Virginia Beach', 'VA','23455','7574550087',NULL,'shark11@cox.net','N','04-OCT-2012');
INSERT INTO dd_donor
VALUES (309, 'Jim', 'Tapp', 'I','200 Pine Tree Blvd.', 'Chesapeake', 'VA','23320','',NULL,'','N','08-OCT-2012');
INSERT INTO dd_donor
VALUES (310, NULL, 'Unique Dezigns', 'B','Connect Circle Unit 12', 'Chesapeake', 'VA','23320','7574442121',NULL,'UDezigns@cox.net','Y','11-SEP-2012');
INSERT INTO dd_project
VALUES (500,'Elders Assistance League', '01-SEP-2012','31-OCT-2012',15000,'Shawn Hasee');
INSERT INTO dd_project
VALUES (501,'Community food pantry #21 freezer equipment', '01-OCT-2012','31-DEC-2012',65000,'Shawn Hasee');
INSERT INTO dd_project
VALUES (502,'Lang Scholarship Fund', '01-JAN-2013','01-NOV-2013',100000,'Traci Brown');
INSERT INTO dd_project
VALUES (503,'Animal shelter Vet Connect Program', '01-DEC-2012','30-MAR-2013',25000,'Traci Brown');
INSERT INTO dd_project
VALUES (504,'Shelter Share Project 2013', '01-FEB-2013','31-JUL-2013',35000,'Traci Brown');
INSERT INTO dd_status
VALUES (10,'Open');
INSERT INTO dd_status
VALUES (20,'Complete');
INSERT INTO dd_status
VALUES (30,'Overdue');
INSERT INTO dd_status
VALUES (40,'Closed');
INSERT INTO dd_status
VALUES (50,'Hold');
INSERT INTO dd_pledge
VALUES (100,303,'18-SEP-2012',80,500,20,NULL,0,738,'Y');
INSERT INTO dd_pledge
VALUES (101,304,'21-SEP-2012',35,500,20,NULL,0,738,'Y');
INSERT INTO dd_pledge
VALUES (102,310,'01-OCT-2012',500,501,20,NULL,0,749,'Y');
INSERT INTO dd_pledge
VALUES (103,307,'03-OCT-2012',2000,501,20,NULL,0,749,'N');
INSERT INTO dd_pledge
VALUES (104,308,'04-OCT-2012',240,501,10,NULL,12,749,'Y');
INSERT INTO dd_pledge
VALUES (105,309,'08-OCT-2012',120,501,10,NULL,12,749,'Y');
INSERT INTO dd_pledge
VALUES (106,301,'12-OCT-2012',75,500,20,NULL,0,738,'N');
INSERT INTO dd_pledge
VALUES (107,302,'15-OCT-2012',1200,501,10,NULL,24,749,'Y');
INSERT INTO dd_pledge
VALUES (108,308,'20-JAN-2013',480,503,10,NULL,24,790,'N');
INSERT INTO dd_pledge
VALUES (109,301,'01-FEB-2013',360,503,10,NULL,12,790,'N');
INSERT INTO dd_pledge
VALUES (110,303,'01-MAR-2013',300,504,10,NULL,12,756,'N');
INSERT INTO dd_pledge
VALUES (111,306,'01-MAR-2013',1500,504,20,NULL,0,756,'Y');
INSERT INTO dd_pledge
VALUES (112,309,'16-MAR-2013',240,504,10,NULL,12,756,'N');
INSERT INTO dd_payment
VALUES (1425,100,80,'18-SEP-2012','CC');
INSERT INTO dd_payment
VALUES (1426,101,35,'21-SEP-2012','DC');
INSERT INTO dd_payment
VALUES (1427,102,500,'01-OCT-2012','CH');
INSERT INTO dd_payment
VALUES (1428,103,2000,'03-OCT-2012','CH');
INSERT INTO dd_payment
VALUES (1429,106,75,'12-OCT-2012','CC');
INSERT INTO dd_payment
VALUES (1430,104,20,'01-NOV-2012','CC');
INSERT INTO dd_payment
VALUES (1431,105,10,'01-NOV-2012','CC');
INSERT INTO dd_payment
VALUES (1432,107,50,'01-NOV-2012','CC');
INSERT INTO dd_payment
VALUES (1433,104,20,'01-DEC-2012','CC');
INSERT INTO dd_payment
VALUES (1434,105,10,'01-DEC-2012','CC');
INSERT INTO dd_payment
VALUES (1435,107,50,'01-DEC-2012','CC');
INSERT INTO dd_payment
VALUES (1436,104,20,'01-JAN-2013','CC');
INSERT INTO dd_payment
VALUES (1437,105,10,'01-JAN-2013','CC');
INSERT INTO dd_payment
VALUES (1438,107,50,'01-JAN-2013','CC');
INSERT INTO dd_payment
VALUES (1439,104,20,'01-FEB-2013','CC');
INSERT INTO dd_payment
VALUES (1440,105,10,'01-FEB-2013','CC');
INSERT INTO dd_payment
VALUES (1441,107,50,'01-FEB-2013','CC');
INSERT INTO dd_payment
VALUES (1442,108,20,'01-FEB-2013','CC');
INSERT INTO dd_payment
VALUES (1443,109,30,'01-FEB-2013','CC');
INSERT INTO dd_payment
VALUES (1444,104,20,'01-MAR-2013','CC');
INSERT INTO dd_payment
VALUES (1445,105,10,'01-MAR-2013','CC');
INSERT INTO dd_payment
VALUES (1446,107,50,'01-MAR-2013','CC');
INSERT INTO dd_payment
VALUES (1447,108,20,'01-MAR-2013','CC');
INSERT INTO dd_payment
VALUES (1448,109,30,'01-MAR-2013','CC');
INSERT INTO dd_payment
VALUES (1449,110,25,'01-MAR-2013','CC');
INSERT INTO dd_payment
VALUES (1450,111,1500,'01-MAR-2013','CH');
INSERT INTO dd_payment
VALUES (1451,104,20,'01-APR-2013','CC');
INSERT INTO dd_payment
VALUES (1452,105,10,'01-APR-2013','CC');
INSERT INTO dd_payment
VALUES (1453,107,50,'01-APR-2013','CC');
INSERT INTO dd_payment
VALUES (1454,108,20,'01-APR-2013','CC');
INSERT INTO dd_payment
VALUES (1455,109,30,'01-APR-2013','CC');
INSERT INTO dd_payment
VALUES (1456,110,25,'01-APR-2013','CC');
INSERT INTO dd_payment
VALUES (1457,112,20,'01-APR-2013','CC');
INSERT INTO dd_payment
VALUES (1458,104,20,'01-MAY-2013','CC');
INSERT INTO dd_payment
VALUES (1459,105,10,'01-MAY-2013','CC');
INSERT INTO dd_payment
VALUES (1460,107,50,'01-MAY-2013','CC');
INSERT INTO dd_payment
VALUES (1461,108,20,'01-MAY-2013','CC');
INSERT INTO dd_payment
VALUES (1462,109,30,'01-MAY-2013','CC');
INSERT INTO dd_payment
VALUES (1463,110,25,'01-MAY-2013','CC');
INSERT INTO dd_payment
VALUES (1464,112,20,'01-MAY-2013','CC');
COMMIT;

Explanation / Answer

CREATE OR REPLACE PACKAGE PLEDGE_PKG
IS
FUNCTION DD_PAYDATE1_PF (IN_PLEDGE_ID NUMBER) RETURN DATE;
FUNCTION DD_PAYEND_PF (IN_PLEDGE_ID NUMBER) RETURN DATE;
END PLEDGE_PKG;

/


CREATE OR REPLACE PACKAGE BODY PLEDGE_PKG
IS
FUNCTION DD_PAYDATE1_PF (IN_PLEDGE_ID NUMBER) RETURN DATE
IS
v_pledge_date   DATE;
v_pay_date      DATE;
BEGIN
SELECT pledgedate INTO v_pledge_date FROM DD_Pledge WHERE idPledge = IN_PLEDGE_ID;
SELECT Last_Day(v_pledge_date)+1 INTO v_pay_date FROM DUAL;
return v_pay_date;
END;
FUNCTION DD_PAYEND_PF (IN_PLEDGE_ID NUMBER) RETURN DATE
IS
v_pledge_date   DATE;
BEGIN
SELECT CASE WHEN paymonths = 0 THEN pledgedate ELSE ADD_MONTHS(pledgedate, paymonths) END INTO v_pledge_date FROM DD_Pledge WHERE idPledge = IN_PLEDGE_ID;
return v_pledge_date;
END;

END;

/


BEGIN
dbms_output.put_line('First payment date for pledge_id 101 is '|| PLEDGE_PKG.DD_PAYDATE1_PF(101) ||' and lst payment date is '||PLEDGE_PKG.DD_PAYEND_PF(101));
dbms_output.put_line('First payment date for pledge_id 110 is '|| PLEDGE_PKG.DD_PAYDATE1_PF(110) ||' and lst payment date is '||PLEDGE_PKG.DD_PAYEND_PF(110));
END;
/

Related Questions

Create a new project and create a class called Account to provide services of a
Question #3620239
Create a new project and name it: HW 1a Create a new file and name it: main.cpp
Question #3648406
Create a new project called FileStats Copy the contents of main.c from CharHisto
Question #3757259
Create a new project called Homework12. The following code shows a simple Java i
Question #3641442
Create a new project called Homework14. The following code shows a simple Java i
Question #664091
Create a new project called LabMartiansIt has 2 files: Martian.javaMartianApp.ja
Question #3820576
Create a new project called simplelist. The following partial code shows a simpl
Question #3638384
Create a new project called simplequeue. The following code shows a simple Java
Question #3639167
Hire Me For All Your Tutoring Needs
Integrity-first tutoring: clear explanations, guidance, and feedback.
Drop an Email at
drjack9650@gmail.com
Chat Now And Get Quote

Navigate

Previous
Create a package called family and execute the following instructions. Create a
Next
Create a package that uses packaged variables to assist in the user logon proces

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.