create or replace
TRIGGER AR_FEE_BANDS
AFTER INSERT OR DELETE OR UPDATE
ON FEE_BANDS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
V_USER CHAR(8);
V_ACTION CHAR(6);
V_DATE TIMESTAMP;
V_REC_SEQ NUMBER;
BEGIN
V_DATE := CURRENT_TIMESTAMP;
IF INSERTING THEN
V_ACTION := 'INSERT';
V_USER := :NEW.MOD_USER;
ELSIF UPDATING THEN
V_ACTION := 'UPDATE';
V_USER := :NEW.MOD_USER;
ELSIF DELETING THEN
V_ACTION := 'DELETE';
V_USER := PKG_USER_UTIL.F_GET_USER;
END IF;
IF INSERTING THEN
SELECT MAX(REC_SEQ) INTO V_REC_SEQ FROM FEE_BANDS_AUD WHERE FEE_RULE=:NEW.FEE_RULE AND AUDIT_ACTION='INSERT';
IF V_REC_SEQ IS NULL THEN
V_REC_SEQ:=0;
INSERT INTO FEE_BANDS_AUD(AUDIT_DATE,AUDIT_ACTION,AUDIT_USER,FEE_BANDS_ID,FEE_RULE,FEE_BAND_LOWER_LIMIT,FEE_BAND_HIGHER_LIMIT,RATE_PERCENT,RATE_NUMBER,CAL_BASED_ON,REC_SEQ)
VALUES(V_DATE, V_ACTION,V_USER,:NEW.FEE_BANDS_ID,:NEW.FEE_RULE,:NEW.FEE_BAND_LOWER_LIMIT,:NEW.FEE_BAND_HIGHER_LIMIT,:NEW.RATE_PERCENT,:NEW.RATE_NUMBER,:NEW.CAL_BASED_ON,V_REC_SEQ+1);
ELSE
INSERT INTO FEE_BANDS_AUD(AUDIT_DATE,AUDIT_ACTION,AUDIT_USER,FEE_BANDS_ID,FEE_RULE,FEE_BAND_LOWER_LIMIT,FEE_BAND_HIGHER_LIMIT,RATE_PERCENT,RATE_NUMBER,CAL_BASED_ON,REC_SEQ)
VALUES(V_DATE, V_ACTION,V_USER,:NEW.FEE_BANDS_ID,:NEW.FEE_RULE,:NEW.FEE_BAND_LOWER_LIMIT,:NEW.FEE_BAND_HIGHER_LIMIT,:NEW.RATE_PERCENT,:NEW.RATE_NUMBER,:NEW.CAL_BASED_ON,V_REC_SEQ+1);
END IF;
ELSIF DELETING THEN
SELECT MAX(REC_SEQ) INTO V_REC_SEQ FROM FEE_BANDS_AUD WHERE FEE_RULE=:OLD.FEE_RULE AND AUDIT_ACTION='DELETE';
IF V_REC_SEQ IS NULL THEN
V_REC_SEQ:=0;
INSERT INTO FEE_BANDS_AUD(AUDIT_DATE,AUDIT_ACTION,AUDIT_USER,FEE_BANDS_ID,FEE_RULE,FEE_BAND_LOWER_LIMIT,FEE_BAND_HIGHER_LIMIT,RATE_PERCENT,RATE_NUMBER,CAL_BASED_ON,REC_SEQ)
VALUES(V_DATE, V_ACTION,V_USER,:OLD.FEE_BANDS_ID,:OLD.FEE_RULE,:OLD.FEE_BAND_LOWER_LIMIT,:OLD.FEE_BAND_HIGHER_LIMIT,:OLD.RATE_PERCENT,:OLD.RATE_NUMBER,:OLD.CAL_BASED_ON,V_REC_SEQ+1);
ELSE
INSERT INTO FEE_BANDS_AUD(AUDIT_DATE,AUDIT_ACTION,AUDIT_USER,FEE_BANDS_ID,FEE_RULE,FEE_BAND_LOWER_LIMIT,FEE_BAND_HIGHER_LIMIT,RATE_PERCENT,RATE_NUMBER,CAL_BASED_ON,REC_SEQ)
VALUES(V_DATE, V_ACTION,V_USER,:OLD.FEE_BANDS_ID,:OLD.FEE_RULE,:OLD.FEE_BAND_LOWER_LIMIT,:OLD.FEE_BAND_HIGHER_LIMIT,:OLD.RATE_PERCENT,:OLD.RATE_NUMBER,:OLD.CAL_BASED_ON,V_REC_SEQ+1);
END IF;
ELSIF UPDATING THEN
SELECT MAX(REC_SEQ) INTO V_REC_SEQ FROM FEE_BANDS_AUD WHERE FEE_RULE=:OLD.FEE_RULE AND AUDIT_ACTION='UPDATE';
IF V_REC_SEQ IS NULL THEN
V_REC_SEQ:=0;
INSERT INTO FEE_BANDS_AUD(AUDIT_DATE,AUDIT_ACTION,AUDIT_USER,FEE_BANDS_ID,FEE_RULE,FEE_BAND_LOWER_LIMIT,FEE_BAND_HIGHER_LIMIT,RATE_PERCENT,RATE_NUMBER,CAL_BASED_ON,REC_SEQ)
VALUES(V_DATE, V_ACTION,V_USER,:OLD.FEE_BANDS_ID,:OLD.FEE_RULE,:OLD.FEE_BAND_LOWER_LIMIT,:OLD.FEE_BAND_HIGHER_LIMIT,:OLD.RATE_PERCENT,:OLD.RATE_NUMBER,:OLD.CAL_BASED_ON,V_REC_SEQ+1);
ELSE
INSERT INTO FEE_BANDS_AUD(AUDIT_DATE,AUDIT_ACTION,AUDIT_USER,FEE_BANDS_ID,FEE_RULE,FEE_BAND_LOWER_LIMIT,FEE_BAND_HIGHER_LIMIT,RATE_PERCENT,RATE_NUMBER,CAL_BASED_ON,REC_SEQ)
VALUES(V_DATE, V_ACTION,V_USER,:OLD.FEE_BANDS_ID,:OLD.FEE_RULE,:OLD.FEE_BAND_LOWER_LIMIT,:OLD.FEE_BAND_HIGHER_LIMIT,:OLD.RATE_PERCENT,:OLD.RATE_NUMBER,:OLD.CAL_BASED_ON,V_REC_SEQ+1);
END IF;
END IF;
END;