Hi,

I am trying to update the p_successful_attendance_flag in the ota_delegate_bookings table. I am using the apps.ota_delegate_booking_api.update_delegate_booking with the below script:

declare

l_ovn number := 1;
l_tfl_object_version_number number := null;
l_finance_line_id number := null;

begin

apps.ota_delegate_booking_api.update_delegate_booking
(p_validate =>true,
p_effective_date => trunc(sysdate),
p_booking_id => 1072572,
p_object_version_number => l_ovn,
p_successful_attendance_flag => 'Y',
p_tfl_object_version_number => l_tfl_object_version_number,
p_finance_line_id => l_finance_line_id
);

end;

When I run this I am getting the below error:

ORA-20001: The primary key specified is invalid

Cause: The primary key values specified are invalid and do not exist in the schema.

Action: Check the primary key values before attempting to carry out the operation again.
ORA-06512: at "APPS.OTA_DELEGATE_BOOKING_API", line 2723
ORA-06512: at line 9

I think this is around the p_tfl_object_version_number and p_finance_line_id parameters, I have checked ota_finance_lines and there are no rows in this table.

Can anyone suggest a way that I can use this API?

Many thanks

Martin

Martin

It suggests to me that the row is not in the OTA_DELEGATE_BOOKINGS table that has that booking_id...

Messages regarding dodgy ovn's usually go on about "Invalid Object" or similar.

Just to humour me, what happens with this query:

SELECT *
FROM OTA_DELEGATE_BOOKINGS
WHERE BOOKING_ID = 1072572;

?

Edited to add: Having looked at the package, the fact that you have omitted the parameter p_pdate_finance_line means that the default of 'N' should not require any action with finance lines.

Just seen on another forum that this is by no means a new, isolated problem... I wonder if it's a bug?

Anyway, you could find out more by tracing your process:

in your test prog, do this:

begin
hr_utility.trace_off;
hr_utility.trace_on();
hr_utility.set_trace_options('TRACE_DEST:DBMS_OUTPUT');

<call your code here>

hr_utility.trace_off;
end;

When you run it (in TOAD, at least) you should get some dbms_output lines with the path the process took - that might shed some more light on where it went wrong!

Indeed, a known bug: 12647700

Thanks CT,

I did think that with the omission of the p_update_finance_line it may not need the finace line information and default to 'N'. Especially when in the table there doesn't seem to be any data referencing the finace table.

I have attached my DBMS trace output, but there wasn't much there:

Entering: OTA_DELEGATE_BOOKING_API. update_delegate_booking 10
Entering: OTA_DELEGATE_BOOKING_BK2.UPDATE_DELEGATE_BOOKING_B 10
Leaving: OTA_DELEGATE_BOOKING_BK2.UPDATE_DELEGATE_BOOKING_B 20
Entering: ota_tdb_shd.lck 5
Leaving: OTA_DELEGATE_BOOKING_API. update_delegate_booking 90

Thank you for the bug information, I will get onto Oracle about this.

Many thanks for your help

Martin

Yep, looks like the problem is in the ota_tdb_shd.lck procedure - this is where the api tries to set up its internal storage with the current state of the booking record prior to applying your required changes. The query is not finding the row, even though it should. Still, the bug fix should sort you out.