OTL API Error

I was trying to update a previously submitted time card using the OTL API (hxc_timestore_deposit.execute_deposit_process). I was getting a very strange error as described below:

ORA-20020: Pa_Otc_Api :::  ->Validate_Otc_Data->Validate_Process->DetermineProcessingFlags :: Only Ovn has changed.  Update the orig_transaction_reference of the ei. : ORA-01403: no data found

I searched a lot and did not find any help in Google or Metalink. Finally I decided to debug the API myself. After spending quite a few hours on the daunting task, I found out the issue.

The API hxc_timestore_deposit.execute_deposit_process, in turn calls another API and a function – Pa_Otc_Api.determineprocessflags(). In this function there is a select statement that retrieves the data from pa_expenditure_items table.

select project_id , task_id, expenditure_type
into l_Proj_Id , l_task_id , l_Exp_Type
from Pa_Expenditure_Items
where RowId = l_RowId;

The above select statement was not retrieving any records and that is the reason why the API was failing. And now the interesting facts:

1. The time card data was not transferred to Oracle Projects and there were no records in pa_expenditure_items table. 

2. Since you are calling the API, please make sure that you set org context as API is using MO Tables

Hope this helps someone who is working on the API. I have used the below APIs in the order given below to update the time card hours:

• hxc_timestore_deposit.update_building_block
• hxc_timestore_deposit.execute_deposit_process