SQL & PL/SQL

(I have Oracle Ebs question, so it may be wrong sub-forum, please correct/remove the topic if needed)

I have table
"fnd_concurrent_requests", this is eBs system table.

How can i determine in any time moment, that a particular concurrent program is scheduled?

I see columns:

concurrent_program_id, Request_ID, parent_request_id, conc_login_id, phase_code, status_code

1. If concurrent is scheduled and waiting next run, then i see that:
(Request_id, phase_code, status_code)= 100, P, I
2. If concurrent schedule time has come, then i see updating and inserting behavior something like:
(Request_id, phase_code, status_code)= 100, P, I
(Request_id, phase_code, status_code)= 100, P, I(Request_id, phase_code, status_code)= 100, R, R ; {was record update}
(Request_id, phase_code, status_code)= 100, C, C ; {was record update}
(Request_id, phase_code, status_code)= 101, P, I ; {was record insert}

Column "parent_request_id" references request_id = 99, which is scheduled (P, I).

I want to query if concurrent program is scheduled or not. Which sql i should write?

I thought this way somehow maybe:

select * from fnd_concurrent_requests r
where r.concurrent_program_id = 10
and ( phase_code, status_code) = ('P','i')

If this would return records, then i would assume that concurrent is sheduled, but this is nor correct because there are sub-states like (R,R) and (C,C) and others perhaps, which actually also say that the program is scheduled, so my query would be wrong.

Maybe other statuses (phase_code, status_code) can be there, which i don't know, or, other aspects, so how i would determine if program is sheduled or not?

Thanks in advance!

Edited by: CharlesRoos on Apr 1, 2009 7:50 AM


Seems like there is lot of statuses btw:
http://arunrathod.blogspot.com/2008/12/meaning-of-statuscode-and-phasecode-in.html

Edited by: CharlesRoos on Apr 1, 2009 7:55 AM