Move PROTECT lines

If a booking with PROTECT has multiple basket items or is exchanged the PROTECT line will remain with the original TICKETS line and show up on the Event Cancellation Refunds report.

Sometimes the finance team will ask for these lines to be "removed" to stop them from showing on the report, they actually need to be moved to another valid booking line, i.e. either the next in a multi basket booking or the new exchanged booking.

Process

Finance will usually provide the Stripe charge ID ch_xxxxxxxxxxx or booking reference A12BCD

Use the charge ID or booking ref to find the relevant transaction lines:

SELECT transaction_id, transactionMasterTransaction_id, 
    transactionLinkedTransaction_id, transactionDateTime, transactionRef, 
    transactionType, transactionAmountGross, transactionCategory, 
    transactionBookedBy, transactionGatewayOrderNumber
FROM DBA.transaction 
WHERE transactionGatewayOrderNumber = 'ch_xxxxxxxxxxx' 
ORDER BY transactionDateTime

Make a note of the OLD TICKET, NEW TICKET and PROTECT transaction IDs.

// OLD: 123456
// PROTECT: 123457
// NEW: 134567

Switch the transactionMasterTransaction_id for the PROTECT line to the NEW transaction ID instead of the OLD transaction ID.

Select all seatingSeat records for the OLD, PROTECT and NEW transactions.

SELECT seatingSeat_id, seatingSection_id, transaction_id, 
    seatingSectionPricing_id
FROM DBA.seatingSeat 
WHERE transaction_id IN(123456,123457,134567)

Update the PROTECT line seatingSection_id to the NEW line's seatingSection_id instead of the OLD one.