The business logic hiding in your database.

1CREATE OR REPLACE PACKAGE BODY pkg_loan AS
2  PROCEDURE nightly_late_fees(p_as_of IN DATE) IS
3    CURSOR c_due IS
4      SELECT l.loan_id, l.next_due_dt, l.pmt_amt, p.grace_days
5        FROM loan l JOIN product p ON p.product_cd = l.product_cd
6       WHERE l.status = 'A' AND l.next_due_dt < p_as_of;
7    v_fee NUMBER(9,2);
8  BEGIN
9    FOR r IN c_due LOOP
10      IF p_as_of - r.next_due_dt > r.grace_days THEN
11        v_fee := GREATEST(15, ROUND(r.pmt_amt * 0.05, 2));
12        INSERT INTO loan_fee (loan_id, fee_type, amount, assessed_dt)
13        VALUES (r.loan_id, 'LATE', v_fee, p_as_of);
14        UPDATE loan SET fee_bal = fee_bal + v_fee
15         WHERE loan_id = r.loan_id;
16      END IF;
17    END LOOP;
18    COMMIT;
19  END nightly_late_fees;
20END pkg_loan;

1CREATE OR REPLACE TRIGGER trg_loan_fee_gl
2AFTER INSERT ON loan_fee
3FOR EACH ROW
4BEGIN
5  INSERT INTO gl_entry (acct, dr_cr, amount, src_ref, posted_dt)
6  VALUES ('4410-LATE-FEE-INC', 'C', :NEW.amount,
7          'FEE:' || :NEW.loan_id, :NEW.assessed_dt);
8  IF :NEW.amount > 100 THEN
9    UPDATE loan SET watch_flag = 'Y' WHERE loan_id = :NEW.loan_id;
10  END IF;
11END;