Searching through tables for a value
I am not sure there is a way to reduce the number of if-statements and nesting. What is possible though is to improve the performance of the conditions you have that check those conditions.

The count(*) is inefficient. Consider adding a 'ROWNUM = 1' condition to your where clause in a way that makes the following conditions possible:

        SELECT COUNT(REQ_ID) INTO requestFound
        FROM FIRSTTABLE
        WHERE FIRSTTABLE.REQ_ID = REQUESTID
          AND (REQ_STATUS = 'D' OR REQ_STATUS = 'A')
          AND ROWNUM = 1;


This will be optimized by Oracle to exit the condition on the first matching result, ending up with a requestFound value that will be either 0, or 1. A value of 1 means there is some data, and does not indicate how much data.

Reading up on ROWNUM is useful. Ask Tom!