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_Q4
IS
    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_Q4
IS
    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_Q5
BEFORE UPDATE OF Active_YN 
ON Suppliers
FOR EACH ROW
BEGIN
    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_Q6
BEFORE INSERT
ON Rental_Invoices
FOR EACH ROW
DECLARE 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;
/