CREATE OR REPLACE PACKAGE BODY prc_mb_body IS
	

FUNCTION fn_logon_request
    	(pIN_teller_id       varchar2,
    	 pIN_pass_word       varchar2)
    	RETURN    NUMBER
IS
   	vnbr_authority_level    tellers.authority_level%TYPE;
 
BEGIN
    	SELECT authority_level
    	INTO vnbr_authority_level
    	FROM tellers
   	WHERE teller_id = pIN_teller_id and pass_word = pIN_pass_word;
    	RETURN (vnbr_authority_level);

EXCEPTION
       WHEN others THEN
       RETURN (-1);

END fn_logon_request;


PROCEDURE sp_customer_add
	(pIN_sin varchar2, pIN_last_name varchar2, pIN_first_name varchar2,
         pIN_middle_name varchar2, pIN_address_line1 varchar2, pIN_address_line2 varchar2,
         pIN_city varchar2, pIN_province varchar2, pIN_country varchar2, pIN_postal varchar2,
         pIN_phone varchar2, pIN_pin varchar2, pIN_teller_id varchar2, pOT_newid OUT number,
         pOT_credit_allowed OUT number, pOT_credit_description OUT varchar2)
IS
  	vNBR_auth_level    tellers.authority_level%type;
BEGIN
   	SELECT authority_level
    	INTO vNBR_auth_level
    	FROM tellers
    	WHERE teller_id = pIN_teller_id;
	
   	IF vNBR_auth_level = 0 THEN
	    raise_application_error(-20200,'Unauthorized access!');
    	ELSIF vNBR_auth_level < 0 THEN
	    raise_application_error(-20201,'Invalid Teller ID.');
    	ELSE
	    hq_national.sp_cust_credit(pIN_sin,pOT_credit_allowed,
	    pOT_credit_description);
	    INSERT INTO customers
	    VALUES(seq_customer_id.NEXTVAL,pIN_sin,pIN_last_name,
	    pIN_first_name,pIN_middle_name,pIN_address_line1,
	    pIN_address_line2,pIN_city,pIN_province,pIN_country,
	    pIN_postal,pIN_phone,pIN_pin);
	    SELECT seq_customer_id.CURRVAL
	    INTO pOT_newid
	    FROM dual;
	    COMMIT;
        END IF; 

END sp_customer_add; 

	
PROCEDURE sp_customer_modify
    	(pIN_customer_id number, pIN_last_name varchar2, pIN_first_name varchar2, 
         pIN_middle_name varchar2, pIN_address_line1 varchar2, pIN_address_line2 varchar2,
         pIN_city varchar2, pIN_province varchar2, pIN_country varchar2, pIN_postal varchar2,
         pIN_phone varchar2, pIN_pin varchar2, pIN_teller_id varchar2)
IS
  	vNBR_auth_level    tellers.authority_level%type;
BEGIN
        SELECT authority_level
        INTO vNBR_auth_level
        FROM tellers
        WHERE teller_id = pIN_teller_id;

        IF vNBR_auth_level = 1 THEN
	    UPDATE customers
	    SET last_name = pIN_last_name, first_name = pIN_first_name,
              middle_name = pIN_middle_name, address_line1 = pIN_address_line1,
              address_line2 = pIN_address_line2, city = pIN_city, province = pIN_province,
              country = pIN_country, postal_code = pIN_postal, phone = pIN_phone, pin = pIN_pin
	    WHERE customer_id = pIN_customer_id;
        ElSE
	    raise_application_error(-20210,'Unauthorized access.'); 
        END IF;

END sp_customer_modify;	

	
PROCEDURE sp_customer_delete
    (pIN_customer_id     NUMBER,
     pIN_teller_id       VARCHAR2)
IS
     vnbr_authority_level  tellers.authority_level%TYPE;
     vEXC_invalid_logon    EXCEPTION;
     vEXC_read_only        EXCEPTION;

BEGIN
     SELECT  authority_level
     INTO    vnbr_authority_level
     FROM    tellers
     WHERE   teller_id = pIN_teller_id;

     IF vnbr_authority_level = 1 THEN
        DELETE FROM customers
        WHERE customer_id = pIN_customer_id;
	Commit;
     ELSIF vnbr_authority_level = 0 THEN
        RAISE vEXC_read_only;
     ELSE
        RAISE vEXC_invalid_logon;
     END IF;

EXCEPTION
     WHEN vEXC_read_only THEN
        RAISE_APPLICATION_ERROR (-20196, 'Teller only has read access privalege');
     WHEN vEXC_invalid_logon THEN
        RAISE_APPLICATION_ERROR (-20197, 'Invalid teller');

END sp_customer_delete;


PROCEDURE SP_CUSTOMER_SEARCH
        (pIN_Search_Name varchar2, pOT_Results OUT varchar2)
IS
	Cursor vcur IS
	SELECT Customer_ID, Last_Name, First_Name, Middle_Name
	FROM Customers
	WHERE lower(Last_Name) like lower('pIN_Search_Name%');
BEGIN
	For vrec in vcur LOOP
	pOT_Results:=pOT_Results || to_char(vrec.Customer_ID) || ';' || initcap(vrec.Last_Name)
        || ',' || initcap(vrec.First_Name) || ' ' || initcap(vrec.Middle_Name) || '*';
	END LOOP;

END sp_customer_search;

	
PROCEDURE SP_CUSTOMER_FIND
	(pIN_Customer_ID number, pOT_Result OUT varchar2)
IS
	vrec Customers%ROWTYPE;
BEGIN
	SELECT *
	INTO vrec
	FROM Customers
	WHERE Customer_ID=pIN_Customer_ID;

	pOT_Result:=to_char(vrec.Customer_ID) || ';' || vrec.SIN || ';' || vrec.Last_Name ||
        ';' || vrec.First_Name || ';' || vrec.Middle_Name || ';' || vrec.Address_Line1 || ';'
        || vrec.Address_Line2 || ';' || vrec.City || ';' || vrec.Province || ';' || 
        vrec.Country || ';' || vrec.Postal_Code || ';' || vrec.Phone || ';' || vrec.PIN || ';';

	IF SQL%NOTFOUND THEN
		raise_application_error(-20007, 'No Record Found.');
	END IF;

END sp_customer_find;

	
PROCEDURE SP_CUSTOMER_FIND
	(pIN_First_Name varchar2, pIN_Last_Name varchar2, pIN_Middle_Name varchar2, 
         pOT_Result OUT varchar2)
IS
	vrec	Customers%Rowtype;
BEGIN
	SELECT *
	INTO vrec
	FROM Customers
	WHERE lower(First_Name)=lower(pIN_First_Name)
	AND lower(Last_Name)=lower(pIN_Last_Name)
	AND lower(Middle_Name)=lower(pIN_Middle_Name);

	pOT_Result:=to_char(vrec.Customer_ID) || ';' || vrec.SIN || ';' || vrec.Last_Name ||
        ';' || vrec.First_Name || ';' || vrec.Middle_Name || ';' || vrec.Address_Line1 || ';'
        || vrec.Address_Line2 || ';' || vrec.City || ';' || vrec.Province || ';' || 
        vrec.Country || ';' || vrec.Postal_Code || ';' || vrec.Phone || ';' || vrec.PIN || ';';

	IF SQL%NOTFOUND THEN
		raise_application_error(-20007, 'No Record Found.');
	END IF;

END sp_customer_find;

	
PROCEDURE SP_CUSTOMER_FIND
	(pIN_SIN number, pOT_Result OUT varchar2)
IS
	vrec	Customers%Rowtype;
BEGIN
	SELECT *
	INTO vrec
	FROM Customers
	WHERE SIN = to_char(pIN_SIN);

	pOT_Result:=to_char(vrec.Customer_ID) || ';' || vrec.SIN || ';' || vrec.Last_Name ||
        ';' || vrec.First_Name || ';' || vrec.Middle_Name || ';' || vrec.Address_Line1 || ';'
        || vrec.Address_Line2 || ';' || vrec.City || ';' || vrec.Province || ';' || 
        vrec.Country || ';' || vrec.Postal_Code || ';' || vrec.Phone || ';' || vrec.PIN || ';';

	IF SQL%NOTFOUND THEN
		raise_application_error(-20007, 'No Record Found.');
	END IF;

END sp_customer_find;

	
PROCEDURE sp_trans_add
	    (pIN_type varchar2, pIN_amount number, pIN_from varchar2, 
             pIN_to varchar2, pIN_teller_id varchar2, pOT_trans_ctr OUT number)
IS
    vNBR_auth_level    tellers.authority_level%type;
    vNBR_interest      hq_national.account_types.interest_rate%type;
    vNBR_account_type  customer_accounts.accounttype_id%type;
BEGIN
    SELECT authority_level
    INTO vNBR_auth_level
    FROM tellers
    WHERE teller_id = pIN_teller_id;

    IF vNBR_auth_level = 1 THEN
	IF pIN_type = 'WTH' THEN
	    UPDATE customer_accounts
	    SET balance = balance - pIN_amount
	    WHERE account_id = pIN_from;
	    
	    
	ELSIF pIN_type = 'DEP' THEN
	    UPDATE customer_accounts
	    SET balance = balance + pIN_amount
	    WHERE account_id = pIN_to;
	ELSIF pIN_type = 'PUR' THEN
	    UPDATE customer_accounts
	    SET balance = balance + pIN_amount
	    WHERE account_id = pIN_from;
	    
	    
	ELSIF pIN_type = 'PAY' THEN
	    UPDATE customer_accounts
	    SET balance = balance - pIN_amount
	    WHERE account_id = pIN_to;
	ELSIF pIN_type = 'TFR' THEN
	    UPDATE customer_accounts
	    SET balance = balance - pIN_amount
	    WHERE account_id = pIN_from;
	    
	    	    
	    UPDATE customer_accounts
	    SET balance = balance + pIN_amount
	    WHERE account_id = pIN_to;
	ELSIF pIN_type = 'INT' THEN
	    SELECT accounttype_id
	    INTO vNBR_account_type
	    FROM customer_accounts
	    WHERE account_id = pIN_to;
	    
	    SELECT interest_rate 
	    INTO vNBR_interest
	    FROM hq_national.account_types
	    WHERE accounttype_id = vNBR_account_type;
	    
	    UPDATE customer_accounts
	    set balance = balance + (balance*vNBR_interest)
	    WHERE account_id = pIN_to;

	ELSE
	   raise_application_error(-20302,'Invalid TransactionType.');
	END IF;
    

    	SELECT seq_transaction_ctr.NEXTVAL
    	INTO pOT_trans_ctr
    	from dual;
    
    	INSERT INTO transactions
   	VALUES(pOT_trans_ctr, sysdate, pIN_type, pIN_amount, pIN_from,
		pIN_to, pIN_teller_id);
    	COMMIT; 
        ELSIF vNBR_auth_level = 0 THEN
	    raise_application_error(-20200,'Unauthorized access!');
        ELSE
	    raise_application_error(-20201,'Invalid Teller ID.');
        END IF;

END sp_trans_add;

	 
PROCEDURE SP_ACCOUNT_ADD
	(pIN_Customer_ID number, pIN_Account_Type number, 
	pOT_Account_ID  OUT varchar2, pIN_Teller_ID varchar2)
IS
	vnbr_limit      customer_accounts.limit%type;
	vNBR_auth_level	tellers.authority_level%type;
BEGIN
	SELECT authority_level
	INTO vNBR_auth_level
	FROM tellers
	WHERE teller_ID=pIN_teller_ID;

	IF vNBR_auth_level=1 THEN 
		SELECT Prefix||seq_account_id.NEXTVAL,limit
		INTO pOT_account_id,vnbr_limit
		FROM HQ_NATIONAL.Account_Types
		WHERE AccountType_ID=pIN_Account_Type;

		IF SQL%FOUND THEN
			INSERT INTO Customer_accounts
		        VALUES(pOT_account_id, pIN_Customer_ID, pIN_Account_Type, 
                               'OPN',0,vnbr_limit);
      		ELSE raise_application_error(-20100,'There is no such Account Type');
		END IF;
	
	ELSE raise_application_error(-20004, 'Invalid Teller ID');
	END IF;

END sp_account_add;

	
PROCEDURE sp_apply_interest
   (pIN_teller_id       VARCHAR2)
IS

    vstr_account_id        customer_accounts.account_id%TYPE;
    vnbr_accounttype_id    customer_accounts.accounttype_id%type;
    vnbr_count             NUMBER;
    vnbr_authority_level   tellers.authority_level%TYPE;
    vnbr_interest          NUMBER;
    vnbr_interest_rate     HQ_NATIONAL.account_types.interest_rate%TYPE;
    vEXC_invalid_logon     EXCEPTION;
    vEXC_read_only         EXCEPTION;
    vnbr_trans_ctr         NUMBER;

    CURSOR customer_accounts_cursor IS
    SELECT accounttype_id, account_id, balance
    FROM customer_accounts;

    customer_accounts_rec      customer_accounts_cursor%ROWTYPE;

BEGIN
    SELECT COUNT(account_id)
    INTO vnbr_count
    FROM customer_accounts;

    SELECT authority_level
    INTO  vnbr_authority_level
    FROM  tellers
    WHERE teller_id = pIN_teller_id;

  IF vnbr_authority_level = 1 THEN

    IF NOT customer_accounts_cursor%ISOPEN THEN
      open customer_accounts_cursor;
    ELSE
     close customer_accounts_cursor;
     open customer_accounts_cursor;
    END IF;
 
    FOR i IN 1..vnbr_count LOOP
      FETCH customer_accounts_cursor INTO customer_accounts_rec;
      vnbr_accounttype_id := customer_accounts_rec.accounttype_id;
      vstr_account_id := customer_accounts_rec.account_id;
     
    SELECT interest_rate
    INTO vnbr_interest_rate
    FROM HQ_NATIONAL.account_types
    WHERE accounttype_id = vnbr_accounttype_id;

    vnbr_interest := customer_accounts_rec.balance *
                           vnbr_interest_rate;
   
    sp_trans_add('INT', vnbr_interest, NULL, vstr_account_id, 
                              pIN_teller_id, vnbr_trans_ctr);
    END LOOP;
    close customer_accounts_cursor;

  ELSIF vnbr_authority_level = 0 THEN
     raise vEXC_read_only;
  ELSE
     raise vEXC_invalid_logon;
  END IF;

EXCEPTION

    WHEN vEXC_read_only THEN
        RAISE_APPLICATION_ERROR(20134, 'Teller only has read access');

    WHEN vEXC_invalid_logon THEN
        RAISE_APPLICATION_ERROR(20133, 'Invalid teller');

END sp_apply_interest;

	
PROCEDURE sp_account_list
    (pIN_customer_id      number,
     pOT_results   OUT    varchar2)
IS
     vnbr_accounttype_id   HQ_NATIONAL.account_types.accounttype_id%TYPE;
     vstr_description      HQ_NATIONAL.account_types.description%TYPE;
     vnbr_count            NUMBER;

     
     CURSOR  account_list_cursor IS
     SELECT accounttype_id, account_id, status, balance, limit
     FROM  customer_accounts
     WHERE customer_id = pIN_customer_id;

     account_list_rec       account_list_cursor%ROWTYPE;

    
BEGIN
    SELECT  count(account_id)
    INTO    vnbr_count
    FROM    customer_accounts
    WHERE   customer_id = pIN_customer_id;

    IF not account_list_cursor%ISOPEN THEN
        open account_list_cursor;
    ELSE
        close account_list_cursor;
        open account_list_cursor;
    END IF;
      
    FOR i IN 1..vnbr_count LOOP
        FETCH account_list_cursor INTO account_list_rec;
        vnbr_accounttype_id := account_list_rec.accounttype_id;

        SELECT description
        INTO vstr_description
        FROM  HQ_NATIONAL.account_types
        WHERE accounttype_id = vnbr_accounttype_id;
	
	pOT_results := pOT_results ||account_list_rec.account_id 
             ||'; '|| vstr_description||'; '|| 
             account_list_rec.status
             ||'; '||to_char(account_list_rec.balance)||'; '||
             to_char(account_list_rec.limit)||'*';
    
      END LOOP;   

EXCEPTION

     WHEN others THEN
        RAISE_APPLICATION_ERROR (-20193, 'Please try again');

END sp_account_list;

END prc_mb_body;
/