CREATE OR REPLACE PROCEDURE PR_Q3(P_First_Name VARCHAR2, P_Last_Name VARCHAR2)AS V_Member_Number NUMBER(6); V_Invoice_Count NUMBER(5); eTooManyOutstanding EXCEPTION;BEGIN SELECT Member_Number INTO V_Member_Number FROM Members WHERE UPPER(First_Name) = UPPER(P_First_Name) AND UPPER(Last_Name) = UPPER(P_Last_Name); SELECT NVL(COUNT(Invoice_Number),0) INTO V_Invoice_Count FROM Rental_Invoices WHERE Member_Number = V_Member_Number AND Invoice_Number IN (SELECT Invoice_Number FROM Rental_Invoice_Details WHERE Overdue_Paid_YN = 'N' AND Overdue_Rental_Rate > 0); IF V_Invoice_Count > 0 THEN RAISE eTooManyOutstanding; END IF; INSERT INTO Rental_Invoices(Invoice_Number, Member_Number,Invoice_Date) VALUES(Seq_Invoice.NEXTVAL, V_Member_Number, SYSDATE); EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20001, 'Member does not exist'); WHEN TOO_MANY_ROWS THEN RAISE_APPLICATION_ERROR(-20002, 'Member name appears in multiple records'); WHEN eTooManyOutstanding THEN RAISE_APPLICATION_ERROR(-20006, 'Member has outstanding rentals, processing discontinued'); WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20003, 'An unexpected error has occured'); END PR_Q3;/CREATE OR REPLACE PACKAGE PKG_Q4IS FUNCTION FN_Q4 (P_First_Name VARCHAR2, P_Last_Name VARCHAR2) RETURN VARCHAR2; FUNCTION FN_Q4 (P_Member_Number NUMBER) RETURN VARCHAR2;END PKG_Q4;/CREATE OR REPLACE PACKAGE BODY PKG_Q4IS FUNCTION FN_Q4 (P_First_Name VARCHAR2, P_Last_Name VARCHAR2) RETURN VARCHAR2 IS V_Member_Number NUMBER(6); V_Invoice_Number NUMBER(5); V_Invoice_Count NUMBER(5); V_Output VARCHAR2(200); eNoOutstanding EXCEPTION; CURSOR C_OUTSTANDING_RENTALS IS SELECT Invoice_Number FROM Rental_Invoice_Details WHERE Invoice_Number IN (SELECT Invoice_Number FROM Rental_Invoices WHERE Member_Number = (SELECT Member_Number FROM Members WHERE UPPER(First_Name) = UPPER(P_First_Name) AND UPPER(Last_Name) = UPPER(P_Last_Name)) AND Overdue_Paid_YN = 'N' AND Overdue_Rental_Rate > 0) GROUP BY Invoice_Number; BEGIN SELECT Member_Number INTO V_Member_Number FROM Members WHERE UPPER(First_Name) = UPPER(P_First_Name) AND UPPER(Last_Name) = UPPER(P_Last_Name); SELECT NVL(COUNT(Invoice_Number),0) INTO V_Invoice_Count FROM Rental_Invoices WHERE Member_Number = V_Member_Number AND Invoice_Number IN (SELECT Invoice_Number FROM Rental_Invoice_Details WHERE Overdue_Paid_YN = 'N' AND Overdue_Rental_Rate > 0); IF V_Invoice_Count = 0 THEN RAISE eNoOutstanding; END IF; V_Output := 'Member ' || V_Member_Number || ', ' || P_First_Name || ' ' || P_Last_Name || ', has the following outstanding invoices: ' ; OPEN C_OUTSTANDING_RENTALS; FETCH C_OUTSTANDING_RENTALS INTO V_Invoice_Number; WHILE C_OUTSTANDING_RENTALS%FOUND LOOP V_Output := V_Output || ' ' || V_Invoice_Number; FETCH C_OUTSTANDING_RENTALS INTO V_Invoice_Number; END LOOP; CLOSE C_OUTSTANDING_RENTALS; RETURN V_Output; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 'Member does not exist'; WHEN TOO_MANY_ROWS THEN RETURN 'Found multiple records bearing that name'; WHEN eNoOutstanding THEN RETURN 'Member ' || V_Member_Number || ', ' || P_First_Name || ' ' || P_Last_Name || ', has no outstanding rentals'; WHEN OTHERS THEN RETURN 'An unexpected error has occured'; RETURN V_Output; END FN_Q4; FUNCTION FN_Q4 (P_Member_Number NUMBER) RETURN VARCHAR2 IS V_First_Name VARCHAR2(25); V_Last_Name VARCHAR2(30); V_Invoice_Number NUMBER(5); V_Invoice_Count NUMBER(5); V_Output VARCHAR2(200); eNoOutstanding EXCEPTION; CURSOR C_OUTSTANDING_RENTALS IS SELECT Invoice_Number FROM Rental_Invoice_Details WHERE Invoice_Number IN (SELECT Invoice_Number FROM Rental_Invoices WHERE Member_Number = P_Member_Number) AND Overdue_Paid_YN = 'N' AND Overdue_Rental_Rate > 0 GROUP BY Invoice_Number; BEGIN SELECT NVL(COUNT(Invoice_Number),0) INTO V_Invoice_Count FROM Rental_Invoices WHERE Member_Number = P_Member_Number AND Invoice_Number IN (SELECT Invoice_Number FROM Rental_Invoice_Details WHERE Overdue_Paid_YN = 'N' AND Overdue_Rental_Rate > 0); SELECT First_Name, Last_Name INTO V_First_Name, V_Last_Name FROM Members WHERE Member_Number = P_Member_Number; IF V_Invoice_Count = 0 THEN RAISE eNoOutstanding; END IF; V_Output := 'Member ' || P_Member_Number || ', ' || V_First_Name || ' ' || V_Last_Name || ', has the following outstanding invoices: ' ; OPEN C_OUTSTANDING_RENTALS; FETCH C_OUTSTANDING_RENTALS INTO V_Invoice_Number; WHILE C_OUTSTANDING_RENTALS%FOUND LOOP V_Output := V_Output || ' ' || V_Invoice_Number; FETCH C_OUTSTANDING_RENTALS INTO V_Invoice_Number; END LOOP; CLOSE C_OUTSTANDING_RENTALS; RETURN V_Output; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 'Member does not exist'; WHEN eNoOutstanding THEN RETURN 'Member ' || P_Member_Number || ', ' || V_First_Name || ' ' || V_Last_Name || ', has no outstanding rentals'; WHEN OTHERS THEN RETURN 'An unexpected error has occured'; RETURN V_Output; END FN_Q4;END PKG_Q4;/CREATE OR REPLACE TRIGGER TR_Q5BEFORE UPDATE OF Active_YN ON SuppliersFOR EACH ROWBEGIN IF :OLD.Active_YN = 'N' AND :NEW.Active_YN = 'Y' THEN RAISE_APPLICATION_ERROR(-20004, 'Suppliers cannot be reactivated'); END IF;END TR_Q5;/CREATE OR REPLACE TRIGGER TR_Q6BEFORE INSERTON Rental_InvoicesFOR EACH ROWDECLARE V_Member_Number NUMBER(6); V_Overdue_Count NUMBER(5); V_Outstanding_Count NUMBER(5);BEGIN SELECT :NEW.Member_Number INTO V_Member_Number FROM dual; SELECT NVL(COUNT(Invoice_Number),0) INTO V_Overdue_Count FROM Rental_Invoices WHERE Invoice_Number IN (SELECT Invoice_Number FROM Rental_Invoice_Details WHERE Date_Returned < SYSDATE) AND Member_Number = V_Member_Number; SELECT NVL(COUNT(Invoice_Number),0) INTO V_Outstanding_Count FROM Rental_Invoices WHERE Member_Number = V_Member_Number AND Invoice_Number IN (SELECT Invoice_Number FROM Rental_Invoice_Details WHERE Overdue_Paid_YN = 'N' AND Overdue_Rental_Rate > 0); IF V_Overdue_Count > 5 OR V_Outstanding_Count > 3 THEN RAISE_APPLICATION_ERROR(-20005,'Member has too many outstanding or overdue rentals'); END IF;END TR_Q6;/