SYS21_DAILY / SYS21_DAILY_DAY Export Hangs/Crashes
Problem: SYS21_DAILY / SYS21_DAILY_DAY Export Hangs/Crashes without giving any error in the front-end.
Cause:
Issue happens due to bad data in the tables.
There are 2 tables affected by this issue:
reservation_daily_element_name
reservation_daily_elements
The issue is that for some reason, even as in PMS the reservation dates show (e.g.) FROM 01-01-20 TO 31-01-20 on the above tables there are records for these reservations with dates outside these dates.
The correction is made in 2 steps and need to be done in this order sequence or you will get errors:
- Delete the wrong values from reservation_daily_element_name
- Delete the wrong values from reservation_daily_elements
Solution :
To correct the issue, firstly you need to run the Detection Script:
SELECT a.resv_name_id
,b.resv_daily_el_seq, b.reservation_date,c.begin_date, c.end_date, c.confirmation_no
FROM reservation_name a
,reservation_daily_element_name b, reservation_name c
WHERE (
b.reservation_date > a.trunc_end_date
OR b.reservation_date < a.trunc_begin_date
)
AND b.resv_name_id = a.resv_name_id
AND (
a.begin_date < sysdate
OR a.begin_date > sysdate
)
AND c.resv_name_id=a.resv_name_id
order by c.confirmation_no;
This will give you a list of reservations similar to this:
We will take the resv_daily_el_seq values from the above list and construct our backup and correction scripts.
In this example we have 18 reservations that are causing issues.
Next step, as you would usually do before tampering with customer data is to create a backup of the original table values, in case you or L2 need to restore them if anything goes wrong:
Create table MAD_rden as select * from reservation_daily_element_name where resv_daily_el_seq in ('12315','377532','2654752','2654760','2654748','2654749','2654750','2654751','2654761','2654753','2654754','2654755','2654756','2654757','2654758','2654759','496275','496274');
Create table MAD_rdel as select * from reservation_daily_elements where resv_daily_el_seq in ('12315','377532','2654752','2654760','2654748','2654749','2654750','2654751','2654761','2654753','2654754','2654755','2654756','2654757','2654758','2654759','496275','496274');
After the above is done, you can then proceed with the correction:
delete from reservation_daily_element_name where resv_daily_el_seq in ('12315','377532','2654752','2654760','2654748','2654749','2654750','2654751','2654761','2654753','2654754','2654755','2654756','2654757','2654758','2654759','496275','496274');
delete from reservation_daily_elements where resv_daily_el_seq in ('12315','377532','2654752','2654760','2654748','2654749','2654750','2654751','2654761','2654753','2654754','2654755','2654756','2654757','2654758','2654759','496275','496274');
Commit your changes afterwards and your issue is solved. You can go back to your export and run it for the affected dates.
The permanent fix for this issue is in 5.6.0.0 so you can recommend site to upgrade, but in the meantime the above steps can be done as a workaround for the moment, until the upgrade happens.