-- Oracle object-relational model
-- Object Type and Body creation

CREATE OR REPLACE TYPE typ_trans AS OBJECT
(proj_id       NUMBER,
 proj_cost     NUMBER,
 overhead_amt  NUMBER,
 sold_amount   NUMBER,
 MEMBER   FUNCTION compute_pl     RETURN NUMBER, 
 MEMBER   FUNCTION compute_status RETURN VARCHAR2
);
/

CREATE OR REPLACE TYPE BODY typ_trans AS
  MEMBER
  FUNCTION compute_pl RETURN NUMBER IS
  BEGIN
    RETURN (sold_amount-proj_cost-overhead_amt);
  END compute_pl;

  MEMBER FUNCTION compute_status  RETURN VARCHAR2 IS
    v_status   VARCHAR2(30);
  BEGIN
    SELECT
     DECODE(SIGN(sold_amount-proj_cost-overhead_amt),
      -1,'Loss', 0,'Break Even', 'Profit')
    INTO  v_status
    FROM  DUAL;
    RETURN  v_status;
  END compute_status;
END;
/



-- Create Table

CREATE TABLE transaction_details
(transaction_id       NUMBER,
 customer_id          NUMBER,
 ot_transaction       typ_trans
);

-- Insert Data (just one row insert shown)

INSERT INTO transaction_details
(transaction_id,
 customer_id,
 transaction_objt)
SELECT
 1, 101, typ_trans(1001,1000,800,2500)
FROM DUAL;
COMMIT;



-- Query Table

SELECT
 t.transaction_id               trans_id,
 t.customer_id                  cust_id,
 t.ot_transaction.proj_id       proj_id,
 t.ot_transaction.proj_cost     proj_cost,
 t.ot_transaction.overhead_amt  overhead,
 t.ot_transaction.sold_amount   sold_amount,
 t.ot_transaction.compute_pl()  amount_pl,
 t.ot_transaction.compute_status()  trans_status
FROM  transaction_details t;