The problem is that you are going into the same table twice. The following SQL should help. I, of course, do not have the tables, so you may need to debug it a little first, but it should give you the idea. Also, this only works in 8i, AFAIK. Before that, you couldn't put an ORDER BY in a sub-select. Also, since you did not provide exact field names in your select, I was unable to remove the QUERY_ROWNUM from the final result. I'll assume this was for simplicity since one would never, of course, use a * in a production SELECT statement ;)

SELECT
*
FROM
(
SELECT
A.* ,
ROWNUM
AS QUERY_ROWNUM
FROM
WL_DETAILS A
WHERE
A.BILLCYCLE = '03'
A.APP_DATE IS NOT NULL
A.SERVICE IS NOT NULL
A.CUSTCODE = '1.611832'
ORDER BY
A.APP_DATE DESC
)
WHERE
QUERY_ROWNUM = 1

Another thought is to replace the IS NOT NULLs with > 'a' or < '1/1/80' or something to eliminate the NULL issue so any indexes on these columns could be used.

Hope this helps,

- Chris