ALTER TABLE PEMBAYARAN_SPPT ADD DISCOUNT FLOAT;
ALTER TABLE PEMBAYARAN_SPPT ADD DISCOUNT_POKOK FLOAT;
ALTER TABLE PEMBAYARAN_SPPT ADD DISCOUNT_DENDA FLOAT;

--------------------------------------------------------------------------------------------------------------------------
-- Trigger saat insert

CREATE OR REPLACE TRIGGER "PBB".INS_PSPPT_DISCOUNT_POKOK 
AFTER INSERT ON "PBB".PEMBAYARAN_SPPT FOR EACH ROW
DECLARE 
    N_SIKLUS_SPPT SPPT.SIKLUS_SPPT%TYPE;
    N_FAKTOR_PENGURANG_SPPT SPPT.FAKTOR_PENGURANG_SPPT%TYPE;
    N_PBB_YG_HARUS_DIBAYAR_SPPT SPPT.PBB_YG_HARUS_DIBAYAR_SPPT%TYPE;
BEGIN
    BEGIN
        SELECT SIKLUS_SPPT, FAKTOR_PENGURANG_SPPT, PBB_YG_HARUS_DIBAYAR_SPPT
        INTO N_SIKLUS_SPPT, N_FAKTOR_PENGURANG_SPPT, N_PBB_YG_HARUS_DIBAYAR_SPPT
        FROM SPPT
        WHERE KD_PROPINSI    = :NEW.KD_PROPINSI
          AND KD_DATI2       = :NEW.KD_DATI2
          AND KD_KECAMATAN   = :NEW.KD_KECAMATAN
          AND KD_KELURAHAN   = :NEW.KD_KELURAHAN
          AND KD_BLOK        = :NEW.KD_BLOK
          AND NO_URUT        = :NEW.NO_URUT
          AND KD_JNS_OP      = :NEW.KD_JNS_OP
          AND THN_PAJAK_SPPT = :NEW.THN_PAJAK_SPPT;
    END;
    
    IF :NEW.DISCOUNT_POKOK > 0 THEN   
        BEGIN
            N_FAKTOR_PENGURANG_SPPT := N_FAKTOR_PENGURANG_SPPT + :NEW.DISCOUNT_POKOK;
            UPDATE SPPT 
            SET SIKLUS_SPPT = N_SIKLUS_SPPT + 1,
                FAKTOR_PENGURANG_SPPT = N_FAKTOR_PENGURANG_SPPT,
                PBB_YG_HARUS_DIBAYAR_SPPT = PBB_TERHUTANG_SPPT - N_FAKTOR_PENGURANG_SPPT,
                TGL_TERBIT_SPPT = SYSDATE
            WHERE KD_PROPINSI    = :NEW.KD_PROPINSI
              AND KD_DATI2       = :NEW.KD_DATI2
              AND KD_KECAMATAN   = :NEW.KD_KECAMATAN
              AND KD_KELURAHAN   = :NEW.KD_KELURAHAN
              AND KD_BLOK        = :NEW.KD_BLOK
              AND NO_URUT        = :NEW.NO_URUT
              AND KD_JNS_OP      = :NEW.KD_JNS_OP
              AND THN_PAJAK_SPPT = :NEW.THN_PAJAK_SPPT;
        END;
    END IF;
END;

----------------------------------------------------------------------------------------------------------------------------------------
-- Trigger update saat terjadi reversal dan perubahan jumlah bayar <> 0

CREATE OR REPLACE
TRIGGER "PBB".UPD_PSPPT_DISCOUNT_POKOK 
AFTER UPDATE ON "PBB".PEMBAYARAN_SPPT 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE 
    N_SIKLUS_SPPT SPPT.SIKLUS_SPPT%TYPE;
    N_FAKTOR_PENGURANG_SPPT SPPT.FAKTOR_PENGURANG_SPPT%TYPE;
    N_PBB_YG_HARUS_DIBAYAR_SPPT SPPT.PBB_YG_HARUS_DIBAYAR_SPPT%TYPE;
    N_DISCOUNTPOKOK PEMBAYARAN_SPPT.DISCOUNT_POKOK%TYPE;
BEGIN
  IF (UPDATING('DISCOUNT_POKOK') AND :OLD.DISCOUNT_POKOK != :NEW.DISCOUNT_POKOK) THEN
    BEGIN
        SELECT SIKLUS_SPPT, FAKTOR_PENGURANG_SPPT, PBB_YG_HARUS_DIBAYAR_SPPT
        INTO N_SIKLUS_SPPT, N_FAKTOR_PENGURANG_SPPT, N_PBB_YG_HARUS_DIBAYAR_SPPT
        FROM SPPT
        WHERE KD_PROPINSI    = :NEW.KD_PROPINSI
          AND KD_DATI2       = :NEW.KD_DATI2
          AND KD_KECAMATAN   = :NEW.KD_KECAMATAN
          AND KD_KELURAHAN   = :NEW.KD_KELURAHAN
          AND KD_BLOK        = :NEW.KD_BLOK
          AND NO_URUT        = :NEW.NO_URUT
          AND KD_JNS_OP      = :NEW.KD_JNS_OP
          AND THN_PAJAK_SPPT = :NEW.THN_PAJAK_SPPT;
    END;
    
    IF (:OLD.DISCOUNT_POKOK != :NEW.DISCOUNT_POKOK) AND :NEW.JML_SPPT_YG_DIBAYAR <> 0 THEN   
        BEGIN
            N_DISCOUNTPOKOK := :NEW.DISCOUNT_POKOK - :OLD.DISCOUNT_POKOK;
            N_FAKTOR_PENGURANG_SPPT := N_FAKTOR_PENGURANG_SPPT + N_DISCOUNTPOKOK;
            
            UPDATE SPPT 
            SET SIKLUS_SPPT = N_SIKLUS_SPPT + 1,
                FAKTOR_PENGURANG_SPPT = N_FAKTOR_PENGURANG_SPPT,
                PBB_YG_HARUS_DIBAYAR_SPPT = PBB_TERHUTANG_SPPT - N_FAKTOR_PENGURANG_SPPT,
                TGL_TERBIT_SPPT = SYSDATE
            WHERE KD_PROPINSI    = :NEW.KD_PROPINSI
              AND KD_DATI2       = :NEW.KD_DATI2
              AND KD_KECAMATAN   = :NEW.KD_KECAMATAN
              AND KD_KELURAHAN   = :NEW.KD_KELURAHAN
              AND KD_BLOK        = :NEW.KD_BLOK
              AND NO_URUT        = :NEW.NO_URUT
              AND KD_JNS_OP      = :NEW.KD_JNS_OP
              AND THN_PAJAK_SPPT = :NEW.THN_PAJAK_SPPT;
        END;
    ELSIF (:OLD.DISCOUNT_POKOK != :NEW.DISCOUNT_POKOK) AND :NEW.JML_SPPT_YG_DIBAYAR = 0 THEN   
        BEGIN
            N_DISCOUNTPOKOK := :NEW.DISCOUNT_POKOK - :OLD.DISCOUNT_POKOK;
            N_FAKTOR_PENGURANG_SPPT := N_FAKTOR_PENGURANG_SPPT + N_DISCOUNTPOKOK;
            
            UPDATE SPPT 
            SET SIKLUS_SPPT = N_SIKLUS_SPPT + 1,
                FAKTOR_PENGURANG_SPPT = N_FAKTOR_PENGURANG_SPPT,
                PBB_YG_HARUS_DIBAYAR_SPPT = PBB_TERHUTANG_SPPT - N_FAKTOR_PENGURANG_SPPT,
                TGL_TERBIT_SPPT = SYSDATE,
                STATUS_PEMBAYARAN_SPPT = '0'
            WHERE KD_PROPINSI    = :NEW.KD_PROPINSI
              AND KD_DATI2       = :NEW.KD_DATI2
              AND KD_KECAMATAN   = :NEW.KD_KECAMATAN
              AND KD_KELURAHAN   = :NEW.KD_KELURAHAN
              AND KD_BLOK        = :NEW.KD_BLOK
              AND NO_URUT        = :NEW.NO_URUT
              AND KD_JNS_OP      = :NEW.KD_JNS_OP
              AND THN_PAJAK_SPPT = :NEW.THN_PAJAK_SPPT;
        END;
    END IF;
  END IF;
END;