1 PACKAGE BODY fnd_rt_request AS
2 /* $Header: AFRTREQB.pls 115.2 99/07/16 23:27:25 porting sh $ */
3
4 PROCEDURE get_test_id(testid IN OUT INTEGER) IS
5 BEGIN
6 SELECT FND_RT_REQUESTS_S.nextval into testid FROM DUAL;
7 END get_test_id;
8
9 PROCEDURE log_request(testid IN INTEGER, requestid IN INTEGER) IS
10 BEGIN
11 INSERT INTO FND_RT_REQUESTS VALUES (testid, requestid);
12 END log_request;
13
14 PROCEDURE search_requests(testid IN INTEGER, timeout IN INTEGER) IS
15
16 cursor children(parent_id number) is
17 select request_id
18 from fnd_concurrent_requests
19 where parent_request_id <> -1
20 and parent_request_id is not null
21 start with request_id = parent_id
22 connect by prior request_id = parent_request_id;
23
24 cursor parents is
25 select request_id
26 from fnd_rt_requests
27 where test_id = testid;
28
29 cnt number;
30 totalsleep integer;
31 BEGIN
32
33
34 for parent in parents loop
35
36 /* Wait until there is no more child running or pending under */
37 /* this parent */
38 cnt := 1;
39 totalsleep := 0;
40 while ((cnt > 0) and (totalsleep < timeout))loop
41 /* the following sleep function is in second */
42 dbms_lock.sleep(60);
43 select count(*) into cnt
44 from fnd_concurrent_requests
45 where phase_code in ('P', 'R')
46 start with request_id = parent.request_id
47 connect by prior request_id = parent_request_id;
48 totalsleep := totalsleep + 1;
49 end loop;
50
51 /* Now is time to fetch all the children for this parent */
52 for child in children(parent.request_id) loop
53 INSERT INTO FND_RT_REQUESTS VALUES (testid, child.request_id);
54 end loop;
55
56 end loop;
57
58 END search_requests;
59
60
61 PROCEDURE get_request(testid IN INTEGER, requestid OUT INTEGER) IS
62 reqid integer;
63 BEGIN
64
65 SELECT REQUEST_ID into reqid
66 FROM FND_RT_REQUESTS
67 WHERE TEST_ID = testid AND ROWNUM=1;
68
69 DELETE FROM fnd_rt_requests
70 WHERE TEST_ID = testid
71 AND REQUEST_ID = reqid;
72
73 requestid := reqid;
74
75 END get_request;
76 END fnd_rt_request;