Assume that in an organization all expenses/revenues one associated with a corpo
ID: 3857027 • Letter: A
Question
Assume that in an organization all expenses/revenues one associated with a corporate account tables CORP-ACCOUNT and CORP-ACCOUNT.HISTORY have the following stemma. An account ID Transaction type ('CR'= '0B'=expense) Transaction amount Transaction done The procedure should update CORP-ACCOUNT by increasing or decreasing the account balance depending on whether the transition B expense of revenue. Also the procedure should insert a record in the CORP_ACCOUNT-HISTORY to record the detest of the transaction. If these is an invalid id. He procedure should gerorate3 an expectation with a custom error code of 21001 and a message of" invalid account id"Explanation / Answer
CREATE [OR REPLACE] PROCEDURE update_Account [
Account_Id IN VARCHAR2,
Transaction_Type IN VARCHAR2,
TRANSACTION_AMOUNT IN NUMBER,
TRANSACTION_DATE IN DATE]
IS
BEGIN
INSERT INTO CORP_ACCOUNT_HISTORY (ACCOUNT_ID,TRANSACTION_TYPE,AMOUNT,TRANSACTION_DATE)
VALUES
(Account_Id, Transaction_Type, TRANSACTION_AMOUNT, TRANSACTION_DATE);
COMMIT;
select COUNT(ACCOUNT_ID)
into N_ACCOUNT From
CORP_ACCOUNT Where ACCOUNT_ID = Account_Id;
IF N_ACCOUNT <> 0 THEN
IF Transaction_Type = ‘CR’ THEN
UPDATE CORP_ACCOUNT SET BALANCE= BALANCE+TRANSACTION_AMOUNT WHERE ACCOUNT_ID = Account_Id;
ELSE
UPDATE CORP_ACCOUNT SET BALANCE= BALANCE-TRANSACTION_AMOUNT WHERE ACCOUNT_ID = Account_Id;
END IF;
ELSE
raise_application_error(21001, ‘Invalid Account Id');
END IF;
END;