Week8 Lab - SQL

file.sql

CREATE OR REPLACE PROCEDURE buildReport (purchaseID IN number)
IS
    FUNCTION GET_TAXRATE (STATE VARCHAR2) RETURN NUMBER IS
        taxrate NUMBER := 0;
    BEGIN
        SELECT TAX_RATE INTO taxrate FROM tax_codes WHERE tax_codes.STATE_CODE = STATE;
        return taxrate;
    END;
    
    FUNCTION CALC_TAX (AMOUNT NUMBER,taxPercent NUMBER) RETURN NUMBER IS
    BEGIN
    -- normally we would just do this math - but whatever - we did it in function. /slowclap
        return Amount*taxPercent;
    END;
    
    PROCEDURE REMOVE_INVENTORY (PRODUCTID IN NUMBER, QTY IN NUMBER) IS
    BEGIN
        UPDATE product SET PRODUCT_AMOUNT=product_amount-QTY WHERE product.Product_ID=PRODUCTID;
    END;
    
    PROCEDURE UPDATE_TOTAL (PURCHASEID IN NUMBER, TOTAL IN NUMBER) IS
    BEGIN
        UPDATE purchase SET TOTAL_PURCHASE=TOTAL WHERE PURCHASE_ID=PURCHASEID;
    END;
    
    PROCEDURE CALCSUBTOTAL(purchaseID IN NUMBER) IS 
            
            purchaserFirstName PURCHASER.PURCHASER_FIRST_NAME%TYPE;
            purchaserLastName PURCHASER.PURCHASER_LAST_NAME%TYPE;
            purchaseDate PURCHASE.PURCHASE_DATE%TYPE;
            
            cursor cur_table IS SELECT * FROM purchase 
                JOIN line_item ON purchase.purchase_id=line_item.FK_purchase_id
                JOIN purchaser ON purchase.FK_PURCHASER_ID=purchaser.purchaser_id
                JOIN product ON product.Product_ID=line_item.FK_PRODUCT_ID
                WHERE PURCHASE.PURCHASE_ID=purchaseID;
                    
            ITER_PRICE NUMBER :=0;
            ITER_TAX NUMBER :=0;
            SUBTOTAL NUMBER :=0;
            TAX NUMBER := 0;
          BEGIN
          --   Print out the purchaser_id, the first name, the last name, the purchase date, and
--the total cost of the purchase (including the calculated sales tax)
            FOR current_item IN cur_table
            LOOP
                -- Feeling Lazy - could refactor to make it only occur once.
                purchaserFirstName := current_item.PURCHASER_FIRST_NAME;
                purchaserLastName :=  current_item.PURCHASER_LAST_NAME;
                purchaseDate := current_item.PURCHASE_DATE;
                
                ITER_PRICE := current_item.PRODUCT_PRICE*current_item.NUMBER_PURCHASED;
                SUBTOTAL := SUBTOTAL + ITER_PRICE;
                ITER_TAX := CALC_TAX(ITER_PRICE,GET_TAXRATE(current_item.state_code));
                TAX := TAX + ITER_TAX;
                dbms_output.put_line( chr(9) || current_item.PRODUCT_NAME ||'(qty:' ||  current_item.NUMBER_PURCHASED || ') SubTotal: ' || ITER_PRICE || ' TAX:' || ITER_TAX );
            END LOOP;
             dbms_output.put_line('-------------------------------------------------------');
             dbms_output.put_line( purchaserFirstName || ' ' || purchaserLastName);
             dbms_output.put_line( chr(9)||'Date: ' || purchaseDate);
             dbms_output.put_line( chr(9)||'SubTotal Cost: ' ||  SUBTOTAL);
             dbms_output.put_line( chr(9)||'Tax: ' ||  TAX);
             dbms_output.put_line('GRAND TOTAL: ' ||  (SUBTOTAL+TAX));
          END;
    BEGIN
    CALCSUBTOTAL(purchaseID);
END;

EXEC buildReport(1000);