运行Retrieve time from OTL（RCVOTLR） 请求时，出现错误RCV_OTL_GNRET_PROCESS_RUNNING

运行Retrieve time from OTL 请求时，出现错误RCV_OTL_GNRET_PROCESS_RUNNING

+---------------------------------------------------------------------------+
Purchasing: Version : 12.0.0

Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.

RCVOTLR module: Retrieve Time from OTL
+---------------------------------------------------------------------------+

Current system time is 24-DEC-2013 12:58:45

+---------------------------------------------------------------------------+

**Starts**24-DEC-2013 12:58:45
**Ends**24-DEC-2013 12:58:46
Your request could not complete because an earlier request is still being processed. Either submit your request later, or contact your system administrator.


+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+

查看了当前用户所有的REQUEST，没有running 的Request； 但另一个同事说他有在运行这个Request,尝试了下面的所有方法，但是都没有解决问题。

1.Cancel掉其他用户的Retrieve time from OTL
2.换用其他的Contingent worker创建新的OTL Timecard

3.给当前的Contingent worker创建新的采购订单。

查看当前的Request是对应的Executable 是RCVOTLR， 类型是PL/SQL Stored Procedure，对应的PL./SQL Package和Procedure 是RCV_HXT_GRP.RETRIEVE_TIMECARDS。

通过FND_NEW_MESSAGES得到的错误代码是RCV_OTL_GNRET_PROCESS_RUNNING

SELECT * 
FROM fnd_new_messages 
WHERE message_text LIKE 'Your request could not complete because an earlier request is still being processed%'

查看相应的procedure RCV_HXT_GRP.RETRIEVE_TIMECARDS,根据错误代码RCV_OTL_GNRET_PROCESS_RUNNING搜索，可以发现是在调用procedure HXC_INTEGRATION_LAYER_V1_GRP.Execute_Retrieval_Process时，发生异常并且异常代码类似 ‘ORA-20001: HXC_0017_GNRET_PROCESS_RUNNING’ 时抛出的。

        BEGIN
            g_generic_start := SYSDATE;

            HXC_INTEGRATION_LAYER_V1_GRP.Execute_Retrieval_Process(
                        P_Process          => 'Purchasing Retrieval Process',
                        P_Transaction_code => NULL,
                        P_Start_Date       => l_start_date,
                        P_End_Date         => l_end_date,
                        P_Incremental      => 'Y',
                        P_Rerun_Flag       => 'N',
                        P_Where_Clause     => l_where_clause,
                        P_Scope            => 'DAY',
                        P_Clusive          => 'EX');

            g_generic_stop := SYSDATE;
            g_generic_time := g_generic_time + (g_generic_stop - g_generic_start);
        EXCEPTION
            WHEN OTHERS THEN
                RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_EXCEPTION
                              , module => l_log_head
                              , message => 'Generic Retrieval failed: ' || SQLERRM
                              );
                IF SQLERRM like 'ORA-20001: HXC_0013_GNRET_NO_BLD_BLKS%' OR
                   SQLERRM like 'ORA-20001: HXC_0012_GNRET_NO_TIMECARDS%' THEN
                    G_CONC_LOG := G_CONC_LOG || FND_MESSAGE.get_string('PO', 'RCV_OTL_GNRET_NO_TIMECARDS')
                                             || FND_GLOBAL.local_chr(10) || FND_GLOBAL.local_chr(10);
                    RAISE SUCCESS_SHORT_CIRCUIT;
                ELSIF SQLERRM like 'ORA-20001: HXC_0017_GNRET_PROCESS_RUNNING%' THEN
                    G_CONC_LOG := G_CONC_LOG || FND_MESSAGE.get_string('PO', 'RCV_OTL_GNRET_PROCESS_RUNNING')
                                             || FND_GLOBAL.local_chr(10) || FND_GLOBAL.local_chr(10);
                END IF;

                RAISE GENERIC_RETRIEVAL_FAILED;
        END;

HXC_INTEGRATION_LAYER_V1_GRP.Execute_Retrieval_Process 后面会调用HXC_GENERIC_RETRIEVAL_PKG.execute_retrieval_process来做实际的retrieve.

在HXC_GENERIC_RETRIEVAL_PKG.execute_retrieval_process搜索‘ORA-20001: HXC_0017_GNRET_PROCESS_RUNNING’，发现 exception为e_process_already_running，才会抛这个错误。

   EXCEPTION
      WHEN e_retrieval_not_registered
      THEN
         fnd_message.set_name ('HXC', 'HXC_0011_GNRET_NOT_REGISTERED');
         fnd_message.raise_error;
      WHEN e_process_already_running
      THEN
         fnd_message.set_name ('HXC', 'HXC_0017_GNRET_PROCESS_RUNNING');
         fnd_message.raise_error;
      WHEN e_no_ranges
      THEN
         fnd_message.set_name ('HXC', 'HXC_0012_GNRET_NO_TIMECARDS');
         fnd_message.raise_error;
      WHEN OTHERS

往上看，可以看到当检查是否有运行的Retrieve 的Concurrennt request时候，返回false的时候就会抛出异常e_process_already_running

               IF NOT check_concurrency_ok
                                  (p_process_id             => g_retrieval_process_id,
                                   p_retrieval_process      => g_params.p_process,
                                   p_where_clause           => g_params.p_where_clause,
                                   p_unique_params          => g_params.p_unique_params
                                  )
               THEN
                  RAISE e_process_already_running;
               END IF;

这里g_params.p_process 是 RCV_HXT_GRP.RETRIEVE_TIMECARDS 传进来的 'Purchasing Retrieval Process'，可以通过下面的SQL获取 p_process_id，这里获得的41

         SELECT rtr.retrieval_process_id, rtr.time_recipient_id,
                rtr.mapping_id
           FROM hxc_retrieval_processes rtr
          WHERE rtr.NAME = 'Purchasing Retrieval Process'

对于'Purchasing Retrieval Process',  function check_concurrency_ok 会通过Cursor 检查在hxc_transactions里有没有正在处理的'Purchasing Retrieval Process'， 如果有，就会返回false,execute_retrieval_process就会抛出异常e_process_already_running. 否则，就会进行正常的Retrieve过程。

         CURSOR chk_transaction
         IS
            SELECT transaction_id
              FROM hxc_transactions tx
             WHERE transaction_process_id = p_process_id
               AND status = 'IN PROGRESS';

简单粗暴的方法就是直接删掉 hxc_transactions里面的这条记录，或者是把它的status update 成其他的东西， 但极有可能会造成timecard 的data corruption，最保险的方法是给Oracle提一个SR。 但由于我是个人的开发测试环境，毫不犹豫的删掉了它，重新提交Retrieve time from OTL，万事OK!