-- see trigger objects
select object_name, timestamp, status
from all_objects
where object_type = 'TRIGGER'
and owner = 'mmannino';



/**** Create a log table for the results ****/
Drop table log_table;
/
drop sequence log_seq;
/
CREATE TABLE log_table
 (ExcNo			INT 	     NOT NULL PRIMARY KEY,
 ExcTrigger		VARCHAR2(25) NOT NULL,
 ExcTable		VARCHAR2(25) NOT NULL,
 ExcKeyValue		Varchar2(15) NOT NULL,
 ExcDate		DATE DEFAULT SYSDATE NOT NULL,
 ExcText		VARCHAR2(255) NOT NULL);

Create SEQUENCE log_seq INCREMENT BY 1;

/**********************************************************/
CREATE OR REPLACE TRIGGER TR_ExceedingLimitNotice_AIU
	AFTER INSERT OR UPDATE OF ExpAmt ON ExpenseItem
	FOR EACH ROW
DECLARE
	v_eclimit NUMBER;
BEGIN
	SELECT a.eclimit 
	INTO v_eclimit
	FROM expcat a 
	WHERE a.ecno = :new.ecno;

	IF :new.ExpAmt > v_eclimit THEN
          dbms_output.put_line('Exp Amt > Limit in TR_ExceedingLimitNotice_AIU trigger');
	  INSERT INTO log_table
	   (ExcNo, ExcTrigger, ExcTable, ExcKeyValue, ExcDate,ExcText)
	   VALUES (log_seq.nextval, 'TR_ExceedingNotice', 'Expenseitem', to_char(:new.eino), SYSDATE, 
		'Warning the expense amount for expense item# ' || :new.eino || 
		' exceeds the limit for the expense category# ' || :new.ecno);
	END IF;
END;
/ 

/******************Testing*********************/
/**** Test Data *****/

/* Test AFter Row trigger */

insert into expensereport
(ERNo, ERDesc, ERSubmitDate, ERStatusDate, ERStatus, SubmitUserNo, ApprUSerNo)
Values (888, 'Testing the After Trigger', Sysdate, Sysdate, 'APPROVED', 1,2);

insert into expenseitem
(EINo, ExpDesc, ExpenseDate,ExpAmt, ExpApprAmt, ERNo, ECNo, AssetNo)
VALUES (666, 'AFter Trigger test item 1', sysdate-1, 99, 99,888,2,null);


/**** Negative Test (over the limit) ****/ 
insert into expenseitem
(EINo, ExpDesc, ExpenseDate,ExpAmt, ExpApprAmt, ERNo, ECNo, AssetNo)
VALUES (777, 'AFter Trigger test item 2', sysdate-1, 101, 101,888,2,null);

-- Should see a new row in the log_table
SELECT * FROM log_table;

/***** Negative Case (Updating Over the limit) ****/
Update expenseitem
	SET expAmt = 300,
            expappramt = 100
	WHERE eino = 666;

-- Should see a new row in the log_table
SELECT * FROM log_table;

/*** Positive case (Updating to equal or lower than limit) ***/

Update expenseitem
	SET expAmt = 100,
            expappramt = 100
	WHERE eino = 777;

-- Should not see a new row in log_table
SELECT * FROM log_table;

ROLLBACK;
-- DELETE should not be necessary
DELETE FROM Log_Table;


/**********************************************************/
CREATE OR REPLACE TRIGGER TR_CheckApprovingUser_BUI
	BEFORE UPDATE OF ApprUserNo ON ExpenseReport
	FOR EACH ROW
DECLARE
	v_ApprOrgNo	Users.UserOrgNo%TYPE;
	v_UserOrgNo	Users.UserOrgNo%TYPE;
	v_UserParentOrgNo Users.UserOrgNo%TYPE;
        ApprovedUserError EXCEPTION;
BEGIN
	SELECT UserOrgNo 
	INTO v_ApprOrgNo
	FROM users 
	WHERE userno = :new.appruserno;
	
	SELECT userorgno
	INTO v_UserOrgNo
	FROM users
	WHERE userno = :new.submituserno;

-- orgparentno is the default value in the decode function
-- if the orgparentno is null, then use v_UserOrgNo
	SELECT DECODE (orgparentno, NULL, v_UserOrgNo, orgparentno)
	INTO v_UserParentOrgNo
	FROM orgunit
	WHERE orgno = v_UserOrgNo;
 	 
	IF v_ApprOrgNo NOT IN (v_UserOrgNo, v_UserParentOrgNo) THEN
	   RAISE ApprovedUserError;
	END IF;

EXCEPTION
	WHEN ApprovedUserError THEN
	  RAISE_APPLICATION_ERROR (-20001,'This user cannot change the status of this report');

END;
/
/********************Test ****/

INSERT INTO orgunit
(OrgNo, OrgName, OrgParentNo)
VALUES (100,'Parent Test Org unit trigger', 1);

INSERT INTO orgunit
(OrgNo, OrgName, OrgParentNo)
VALUES (101,'Test Org unit trigger', 100);

INSERT INTO users
(UserNo, UserFirstName, UserLastName, UserPhone, UserEmail, UserOrgNo)
VALUES (333, 'ParentOrgUnit', 'TestUser1', '303-999-8888', 'test@user1' , 100);

INSERT INTO users
(UserNo, UserFirstName, UserLastName, UserPhone, UserEmail, UserOrgNo)
VALUES (444, 'SameOrgUnit', 'TestUser2', '303-999-8888', 'test@user2' , 100);

INSERT INTO users
(UserNo, UserFirstName, UserLastName, UserPhone, UserEmail, UserOrgNo)
VALUES (555, 'OrgUnit', 'TestUser3', '303-999-8888', 'test@user3' , 101);

INSERT INTO ExpenseReport
(ERNO, ERDESC, ERSUBMITDATE, ERSTATUSDATE, ERSTATUS, SUBMITUSERNO)      
Values (9999, 'Org Unit Trigger Test ER', Sysdate, Sysdate, 'PENDING', 555);


/***Negative Test Case (non authorized user) ****/
UPDATE ExpenseReport set 
	ERstatus = 'DENIED',
	appruserno = 1
WHERE erno = 9999;

/**** Positive Test Case ****/
UPDATE ExpenseReport set 
	ERstatus = 'DENIED',
	appruserno = 444
WHERE erno = 9999;

/**** Positive Test Case ****/
UPDATE ExpenseReport set 
	ERstatus = 'DENIED',
	appruserno = 333
WHERE erno = 9999;

ROLLBACK;

/**********************************************************/
CREATE OR REPLACE TRIGGER TR_ChangeStatusCase_BIU
	BEFORE INSERT OR UPDATE OF ERStatus ON ExpenseReport
	FOR EACH ROW
BEGIN
	:new.erstatus := UPPER(:new.erstatus);

END;
/
/*** Positive test cases ***/
INSERT INTO orgunit
(OrgNo, OrgName)
VALUES (101,'Test Org unit trigger');

INSERT INTO users
(UserNo, UserFirstName, UserLastName, UserPhone, UserEmail, UserOrgNo)
VALUES (555, 'OrgUnit', 'TestUser3', '303-999-8888', 'test@user3' , 101);

INSERT INTO ExpenseReport
(ERNO, ERDESC, ERSUBMITDATE, ERSTATUSDATE, ERSTATUS, SUBMITUSERNO)      
Values (9999, 'Org Unit Trigger Test ER', Sysdate, Sysdate, 'pENdINg', 555);

SELECT * FROM ExpenseReport WHERE ERNo = 9999;

Update expensereport
set erstatus = 'pending'
where erno = 9999;

SELECT * FROM ExpenseReport WHERE ERNo = 9999;

ROLLBACK;
/


/**************Update Items Expense Date Trigger*****/

CREATE OR REPLACE TRIGGER TR_ExpenseDate_BIU
	BEFORE INSERT OR UPDATE of ExpenseDate ON ExpenseItem
	FOR EACH ROW
DECLARE
	v_SubmitDate	ExpenseReport.ERSubmitDate%TYPE;
	v_eino		ExpenseItem.EINo%TYPE;
        ErrMessage      VARCHAR2(255);
BEGIN
	v_eino := :new.eino;
	SELECT b.ERSubmitDate
	INTO v_SubmitDate
	FROM expensereport b
	WHERE :new.erno = b.erno;
	
        IF v_SubmitDate < :new.expensedate THEN
          ERRMessage := 'Expense Date: ' || to_char(:NEW.ExpenseDate) || ' greater than ';
          ERRMessage := ErrMessage || 'submit date: ' || to_char(v_SubmitDate) || '.';
	  RAISE_APPLICATION_ERROR (-20001,ErrMessage);
	END IF;
END;
/

/***** Test Data ******/

insert into expensereport
(ERNo, ERDesc, ERSubmitDate, ERStatusDate, ERStatus, SubmitUserNo, ApprUSerNo)
Values (111, 'Testing the expense date Trigger', to_date('8-Feb-2003'), to_date('8-Feb-2003'), 'APPROVED', 1,2);

/***Negative Test Case**/
INSERT INTO expenseitem
(EINo, ExpDesc, ExpenseDate,ExpAmt, ExpApprAmt, ERNo, ECNo, AssetNo)
VALUES (987, 'AFter ExpenseDate test item 1', to_date('8-Feb-2003')+1, 99, 99,111,2,null);

/*** Positive test case***/
-- This test does not seem to work. It should be positive but it fails.
-- Using sysdate includes time information so equal dates cannot be tested.
-- INSERT INTO expenseitem
-- (EINo, ExpDesc, ExpenseDate,ExpAmt, ExpApprAmt, ERNo, ECNo, AssetNo)
-- VALUES (987, 'Ater ExpenseDate test item 1', sysdate-1, 99, 99,111,2,null);

INSERT INTO expenseitem
(EINo, ExpDesc, ExpenseDate,ExpAmt, ExpApprAmt, ERNo, ECNo, AssetNo)
VALUES (987, 'Ater ExpenseDate test item 1', to_date('8-Feb-2003'), 99, 99,111,2,null);

/***Negative Test Case**/
Update expenseitem
set expensedate = to_date('8-Feb-2003')+1
where eino = 987;

ROLLBACK;

/******************************** Roll up an expense Item ************************/
CREATE OR REPLACE PROCEDURE spRollUpExpenseItem
(P_ERNo IN expenseitem.erno%TYPE, P_ECNo IN ExpenseItem.ECNo%TYPE, P_Amt IN NUMBER, P_Result OUT BOOLEAN)
AS
	v_OrgNo		OrgUnit.orgno%TYPE;
	v_EcNo		expcat.ecno%TYPE;
	v_ERstatus  expensereport.erstatus%TYPE;
	v_ApprAmt   expenseitem.expappramt%TYPE;
	v_BIyear	budgetitem.biyear%TYPE;	
	e_NonApprovedER	EXCEPTION;
BEGIN		
	SELECT to_number(to_char(b.ersubmitdate, 'YYYY')), c.userorgno, b.erstatus
	INTO v_biyear, v_orgno, v_erstatus
	FROM expensereport b, users c
	WHERE p_erno = b.erno AND
	      b.submituserno = c.userno;

	/* make sure the expense report is approved*/	
	IF v_ERstatus ='DENIED' OR v_ERstatus = 'PENDING' THEN
		RAISE e_NonApprovedER;	
	END IF;

	Update BudgetItem a
	Set	a.biactual = a.biactual + P_Amt
	WHERE
		a.biyear = v_biyear AND
		a.orgno = v_orgno AND
		a.ecno = p_ecno;
        P_Result := TRUE;
EXCEPTION
	WHEN e_NonApprovedER THEN
         P_Result := FALSE;
        WHEN OTHERS THEN
         raise_application_error(-20001, 'Error Number: ' || to_char(SQLCODE) || ' Error Message: ' || SQLERRM);
END;
/

-- Test cases are not needed for this procedure. The test cases are needed for the trigger.

-- Expense report test data
insert into expensereport
	(ERNo, ERDesc, ERSubmitDate, ERStatusDate, ERStatus, SubmitUserNo, ApprUSerNo)
	Values (1000, 'The First Test ER', Sysdate -1, Sysdate, 'APPROVED', 2,1);
insert into expensereport
	(ERNo, ERDesc, ERSubmitDate, ERStatusDate, ERStatus, SubmitUserNo)      
	Values (1001, 'The Second Test ER', Sysdate, Sysdate, 'PENDING', 2);
insert into expensereport      
	(ERNo, ERDesc, ERSubmitDate, ERStatusDate, ERStatus, SubmitUserNo, ApprUSerNo)
	Values (1002, 'The third test ER', Sysdate, Sysdate, 'DENIED', 2,1);

/****** Expense item test data ******/
insert into expenseitem
	(EINo, ExpDesc, ExpenseDate,ExpAmt, ExpApprAmt, ERNo, ECNo, AssetNo)
	values (100, 'test item 1', sysdate, 20, 20,1000,1,2); 

insert into expenseitem
	(EINo, ExpDesc, ExpenseDate,ExpAmt, ExpApprAmt, ERNo, ECNo, AssetNo)
	values (101, 'test item 2', sysdate, 20, 20,1001,1,2);

insert into expenseitem
	(EINo, ExpDesc, ExpenseDate,ExpAmt, ExpApprAmt, ERNo, ECNo, AssetNo)
	values (102, 'test item 3', sysdate, 40, 20,1002,1,2);

/****** Budget item test data ******/

insert into budgetitem
	(BINo, BIYear, OrgNo, ECNo, BIAmt, BIActual)
	VALUES (1000, 2003, 6,1,20.00,0.00);

/****** Call script for positive test ******/
DECLARE
 v_Result BOOLEAN;
BEGIN
 spRollUpExpenseItem (1000, 1, 100, v_Result);
 IF v_Result THEN
    dbms_output.put_line('Budget Item table updated.');
 ELSE
    dbms_output.put_line('Budget Item table not updated.');
 END IF;
END;
/

/****** Call script for negative tests ******/
DECLARE
 v_Result BOOLEAN;
BEGIN
-- Pending expense report
 spRollUpExpenseItem (1001, 1, 100, v_Result);
 IF v_Result THEN
    dbms_output.put_line('Budget Item table updated.');
 ELSE
    dbms_output.put_line('Budget Item table not updated.');
 END IF;

-- Denied expense report
 spRollUpExpenseItem (1002, 1, 100, v_Result);
 IF v_Result THEN
    dbms_output.put_line('Budget Item table updated.');
 ELSE
    dbms_output.put_line('Budget Item table not updated.');
 END IF;
END;
/

ROLLBACK;
-- These delete statements should not be necessary
DELETE FROM BudgetItem WHERE BINo = 1000;
DELETE FROM ExpenseReport WHERE ERNo > 999;


/**********************************************************/
CREATE OR REPLACE TRIGGER TR_RollupExpAmt_AUD
	AFTER DELETE OR UPDATE OF ExpApprAmt ON ExpenseItem
	FOR EACH ROW
DECLARE
	RollupResult BOOLEAN;
        RollupError  EXCEPTION;
        RollupStatus VARCHAR2(100);
        tmpEINo      ExpenseItem.EINo%TYPE;
BEGIN
	IF UPDATING THEN
	  /* rollup amount is new - old approved amount */
          RollupStatus := 'Update operation. Update of BIActual: ' || to_char(:NEW.ExpApprAmt - :OLD.ExpApprAmt);
          tmpEINo := :NEW.EINo;
--          spRollupExpenseItem(:NEW.EINo, :NEW.ExpApprAmt - :OLD.ExpApprAmt, RollupResult);	
            spRollupExpenseItem(:NEW.ERNo, :NEW.ECNo, :NEW.ExpApprAmt - :OLD.ExpApprAmt, RollupResult);	
	ELSE
	  /* Deleting expense item: subtract approved amount */
          tmpEINo := :OLD.EINo;
          RollupStatus := 'Delete operation. Update of BIActual: ' || to_char(-:OLD.ExpApprAmt);
          spRollupExpenseItem(:OLD.ERNo, :OLD.ECNo, -:OLD.ExpApprAmt, RollupResult);	
--        spRollupExpenseItem(:OLD.EINo, -:OLD.ExpApprAmt, RollupResult);	
	END IF;
	IF RollupResult THEN
	   INSERT INTO log_table
	   (ExcNo, ExcTrigger, ExcTable, ExcKeyValue, ExcDate,ExcText)
	    VALUES (log_seq.nextval, 'TR_RollupExpAmt_AUD', 'ExpenseItem', to_char(tmpEINo), SYSDATE, RollupStatus);
	END IF;
END;
/

/*** TEST DATA **/
INSERT INTO ExpCat
	(ECNo, ECName, ECLimit)
	VALUES (1000,'Gas',1000.00);

insert into expensereport
	(ERNo, ERDesc, ERSubmitDate, ERStatusDate, ERStatus, SubmitUserNo, ApprUSerNo)
	Values (1000, 'The Rollup Trigger expense Test', Sysdate -1, Sysdate, 'APPROVED', 2,1);

insert into expenseitem
	(EINo, ExpDesc, ExpenseDate,ExpAmt, ExpApprAmt, ERNo, ECNo, AssetNo)
	values (100, 'test item 1', sysdate-2, 20, 20,1000,1000,2);
	
insert into expenseitem
	(EINo, ExpDesc, ExpenseDate,ExpAmt, ExpApprAmt, ERNo, ECNo, AssetNo)
	values (101, 'test item 2', sysdate-2, 40, 40,1000,1000,2);

insert into budgetitem
	(BINo, BIYear, OrgNo, ECNo, BIAmt, BIActual)
	VALUES (1000, 2005, 6,1000,100.00,60.00);

/**** Update test ****/
update expenseitem
set expamt = 40,
    expappramt = 40
where eino = 100;
-- Should increase BIActual by 20
SELECT * FROM BudgetItem WHERE BINo = 1000;
-- Should add a row to the Log_Table
SELECT * FROM Log_Table;

/**** UPDATE TEST ***/
update expenseitem
set expamt = 10,
    expappramt = 10
where eino = 100;
-- Should decrease BIActual by 30
SELECT * FROM BudgetItem WHERE BINo = 1000;
-- Should add a row to the Log_Table
SELECT * FROM Log_Table;

/*** Delete test ***/
delete from expenseitem where
eino = 101;
-- Should decrease BIActual by 40
SELECT * FROM BudgetItem WHERE BINo = 1000;
-- Should add a row to the Log_Table
SELECT * FROM Log_Table;

ROLLBACK;

/*** Same tests except for non approved expense report ***/
-- Each test should have not effect on the BudgetItem row and no exception row.
/*** TEST DATA **/
INSERT INTO ExpCat
	(ECNo, ECName, ECLimit)
	VALUES (1000,'Gas',1000.00);

insert into expensereport
	(ERNo, ERDesc, ERSubmitDate, ERStatusDate, ERStatus, SubmitUserNo, ApprUSerNo)
	Values (1000, 'The Rollup Trigger expense Test', Sysdate -1, Sysdate, 'PENDING', 2,1);

insert into expenseitem
	(EINo, ExpDesc, ExpenseDate,ExpAmt, ExpApprAmt, ERNo, ECNo, AssetNo)
	values (100, 'test item 1', sysdate-2, 20, 20,1000,1000,2);
	
insert into expenseitem
	(EINo, ExpDesc, ExpenseDate,ExpAmt, ExpApprAmt, ERNo, ECNo, AssetNo)
	values (101, 'test item 2', sysdate-2, 40, 40,1000,1000,2);

insert into budgetitem
	(BINo, BIYear, OrgNo, ECNo, BIAmt, BIActual)
	VALUES (1000, 2005, 6,1000,100.00,60.00);

/**** Update test ****/
update expenseitem
set expamt = 40,
    expappramt = 40
where eino = 100;
-- Should NOT increase BIActual by 20
SELECT * FROM BudgetItem WHERE BINo = 1000;
-- Should NOT add a row to the Log_Table
SELECT * FROM Log_Table;

/**** UPDATE TEST ***/
update expenseitem
set expamt = 10,
    expappramt = 10
where eino = 100;
-- Should NOT decrease BIActual by 10
SELECT * FROM BudgetItem WHERE BINo = 1000;
-- Should NOT add a row to the Log_Table
SELECT * FROM Log_Table;

/*** Delete test ***/
delete from expenseitem where
eino = 101;
-- Should NOT decrease BIActual by 40
SELECT * FROM BudgetItem WHERE BINo = 1000;
-- Should NOT add a row to the Log_Table
SELECT * FROM Log_Table;

ROLLBACK;