How to get request_id of previous request in a request set?

最新推荐文章于 2024-10-31 18:35:46 发布

原创最新推荐文章于 2024-10-31 18:35:46 发布 · 2.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#function #c

Oracle Apps 专栏收录该内容

39 篇文章

订阅专栏

本文介绍了一个Oracle PL/SQL函数，该函数用于通过指定的并发程序名称来获取其前置请求ID。此过程涉及从数据库中读取请求记录及其父级请求信息，并通过连接遍历的方式找到符合条件的第一个请求。

FUNCTION get_prev_req_id(p_conc_program IN VARCHAR2) RETURN VARCHAR2
IS
    l_parent_req_id         NUMBER;
    l_request_id            NUMBER;
BEGIN
    SELECT parent_request_id
      INTO l_parent_req_id
      FROM fnd_concurrent_requests
     WHERE request_id = FND_PROFILE.VALUE('CONC_REQUEST_ID');
    FOR c IN (SELECT request_id,fcpt.user_concurrent_program_name
                FROM (SELECT     request_id, concurrent_program_id
                            FROM fnd_concurrent_requests aaa
                      START WITH request_id = l_parent_req_id
                      CONNECT BY PRIOR request_id = parent_request_id) cps, fnd_concurrent_programs_tl fcpt
               WHERE cps.concurrent_program_id = fcpt.concurrent_program_id
                 AND fcpt.user_concurrent_program_name LIKE p_conc_program
               ORDER BY request_id)
    LOOP
      l_request_id := c.request_id;
      EXIT;
    END LOOP;
    RETURN l_request_id ;
END;