SQL & PL/SQL

Hi All,

Please help me to resolve this issue:

if PSSTATUS = 'A' or 'P' then I need to execute the steps else come out of the trigger. But here when the SELECT statement returns no rows I am getting 'ORA-01403: no data found' message while debugging through TOAD and through application the PSSTATUS is not changing at all.

CREATE OR REPLACE TRIGGER IBSADMIN.A_U_PRODSUBS AFTER UPDATE ON IBSADMIN.PRODSUBS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
WHEN (
NEW.PSSTATUS = 'A' OR NEW.PSSTATUS = 'P'
)
DECLARE
VACCNR NUMBER;
VDAY NUMBER;
VNID NUMBER;

BEGIN
VACCNR := 0;
VDAY := 0;
VNID := 0;

SELECT NVL(SUINVOIC.ACCOUNTNR,0) INTO VACCNR
FROM IBSADMIN.ADDRESS, IBSADMIN.SUINVOIC
WHERE ADDRESS.ADDRCUSTNR = SUINVOIC.ACCSUBSCRIBERNR
AND ADDRESS.ADDREVENTNR = 100
AND ADDRESS.ADDRCOUNTRY = 'K'
AND SUINVOIC.ACCMOP = 'P'
AND SUINVOIC.ACCACCOUNTTYPE = 'N'
AND SUINVOIC.ACCOUNTNR = :NEW.PSACCNR
AND ADDRESS.ADDRCUSTNR = :NEW.PSSUBSCRIBERNR;

IF VACCNR > 0 THEN

VDAY := TO_CHAR(SYSDATE, 'DD');

IF VDAY >= 1 AND VDAY <= 15 THEN
VNID := ORACLE_TO_MAGIC_DATE(LAST_DAY(SYSDATE) + 1) ;
UPDATE IBSADMIN.SUINVOIC SET SUINVOIC.ACCNEXTINVOICEDAT = VNID WHERE SUINVOIC.ACCOUNTNR = VACCNR;
ELSE
VNID := ORACLE_TO_MAGIC_DATE(ADD_MONTHS((LAST_DAY(SYSDATE) + 1),1));
UPDATE IBSADMIN.SUINVOIC SET SUINVOIC.ACCNEXTINVOICEDAT = VNID WHERE SUINVOIC.ACCOUNTNR = VACCNR;
END IF;
END IF;

END A_U_PRODSUBS;
/

Regards,
Bimal