aonestar.public

Parameters

Name	Type	Mode
user_name	text	IN
status	text	INOUT
msg	text	INOUT

Definition

DECLARE
    v_system_date date       = fn_system_date();
    v_backup_days interval   = (fn_prop_config('ARCHIVE_MOVE_DAYS', '90') || ' days')::interval;
    v_clearing_days interval = (fn_prop_config('ARCHIVE_PURGE_DAYS', '280') || ' days')::interval;
    rec_count int = 0;
    deleted_rows int = 0;
    archived_rows int = 0;
    archive_batch_rows int = 0;
    table_name TEXT;
    _sqlstate TEXT;
    _detail   TEXT;
    _hint     TEXT;
    _context  TEXT;
    _msg_text TEXT;
    blocked_count int = 0;
BEGIN
    IF to_regclass('pg_temp.tmp_endday_archive_reg_ids') IS NULL THEN
        CREATE TEMP TABLE tmp_endday_archive_reg_ids (
            register_id int PRIMARY KEY,
            date_ref date NOT NULL
        ) ON COMMIT DROP;
    END IF;

IF to_regclass('pg_temp.tmp_endday_archive_booking_ids') IS NULL THEN
        CREATE TEMP TABLE tmp_endday_archive_booking_ids (
            booking_id int PRIMARY KEY,
            date_ref date NOT NULL
        ) ON COMMIT DROP;
    END IF;

IF to_regclass('pg_temp.tmp_endday_archive_folio_ids') IS NULL THEN
        CREATE TEMP TABLE tmp_endday_archive_folio_ids (
            folio_id int PRIMARY KEY,
            date_ref date NOT NULL,
            owner_type text NOT NULL,
            owner_id int NOT NULL
        ) ON COMMIT DROP;
    END IF;

TRUNCATE tmp_endday_archive_reg_ids;
    TRUNCATE tmp_endday_archive_booking_ids;
    TRUNCATE tmp_endday_archive_folio_ids;

INSERT INTO tmp_endday_archive_reg_ids(register_id, date_ref)
    SELECT rg.id, GREATEST(
            rg.departure,
            COALESCE(b.departure, rg.departure),
            COALESCE(MAX(f.close_date), rg.departure)
        ) AS date_ref
    FROM registration rg
        LEFT JOIN booking b ON b.id = rg.booking_id
        LEFT JOIN folio f ON f.register_id = rg.id
    WHERE rg.status IN ('O','X')
    GROUP BY rg.id, rg.departure, b.departure
    HAVING GREATEST(
            rg.departure,
            COALESCE(b.departure, rg.departure),
            COALESCE(MAX(f.close_date), rg.departure)
        ) + v_backup_days <= v_system_date;

INSERT INTO tmp_endday_archive_booking_ids(booking_id, date_ref)
    SELECT b.id, GREATEST(b.departure, COALESCE(MAX(f.close_date), b.departure)) AS date_ref
    FROM booking b
        LEFT JOIN folio f ON f.booking_id = b.id
    WHERE ((b.departure + v_backup_days <= v_system_date) OR (b.status = 'W' AND b.arrival = v_system_date))
      AND NOT EXISTS(
          SELECT 1
          FROM registration rg
          WHERE rg.booking_id = b.id
            AND NOT EXISTS (
                SELECT 1
                FROM tmp_endday_archive_reg_ids tr
                WHERE tr.register_id = rg.id
            )
      )
    GROUP BY b.id, b.departure, b.status, b.arrival;

INSERT INTO tmp_endday_archive_folio_ids(folio_id, date_ref, owner_type, owner_id)
    SELECT f.id, f.close_date, 'C', f.id
    FROM folio f
    WHERE f.status = 'C'
      AND f.folio_type = 'C'
      AND f.close_date + v_backup_days <= v_system_date
    ON CONFLICT DO NOTHING;

INSERT INTO tmp_endday_archive_folio_ids(folio_id, date_ref, owner_type, owner_id)
    SELECT f.id, GREATEST(r.date_ref, COALESCE(f.close_date, r.date_ref)), 'R', r.register_id
    FROM tmp_endday_archive_reg_ids r
    JOIN folio f ON f.register_id = r.register_id
    ON CONFLICT DO NOTHING;

INSERT INTO tmp_endday_archive_folio_ids(folio_id, date_ref, owner_type, owner_id)
    SELECT f.id, GREATEST(b.date_ref, COALESCE(f.close_date, b.date_ref)), 'B', b.booking_id
    FROM tmp_endday_archive_booking_ids b
    JOIN folio f ON f.booking_id = b.booking_id
    ON CONFLICT DO NOTHING;

WITH candidate_tran AS (
        SELECT tf.owner_type, tf.owner_id, t.id, t.transfer_src, t.transfer_des
        FROM tmp_endday_archive_folio_ids tf
        JOIN transactions t ON t.folio_id = tf.folio_id
    ),
    blocked_owner AS (
        SELECT DISTINCT ct.owner_type, ct.owner_id
        FROM candidate_tran ct
        JOIN transactions ref ON ref.id IN (ct.transfer_src, ct.transfer_des)
        LEFT JOIN candidate_tran ref_ct ON ref_ct.id = ref.id
        WHERE ref_ct.id IS NULL
        UNION
        SELECT DISTINCT ct.owner_type, ct.owner_id
        FROM candidate_tran ct
        JOIN transactions ref ON ref.transfer_src = ct.id OR ref.transfer_des = ct.id
        LEFT JOIN candidate_tran ref_ct ON ref_ct.id = ref.id
        WHERE ref_ct.id IS NULL
    )
    SELECT COUNT(*) INTO blocked_count FROM blocked_owner;

IF blocked_count > 0 THEN
        PERFORM sp_log_error(
            'sp_endday_clear_data',
            format('Skipped %s archive owner set(s) because transfer transactions reference active rows outside the archive batch', blocked_count),
            null, null, null, null,
            (
                WITH candidate_tran AS (
                    SELECT tf.owner_type, tf.owner_id, t.id, t.transfer_src, t.transfer_des
                    FROM tmp_endday_archive_folio_ids tf
                    JOIN transactions t ON t.folio_id = tf.folio_id
                ),
                blocked_owner AS (
                    SELECT DISTINCT ct.owner_type, ct.owner_id
                    FROM candidate_tran ct
                    JOIN transactions ref ON ref.id IN (ct.transfer_src, ct.transfer_des)
                    LEFT JOIN candidate_tran ref_ct ON ref_ct.id = ref.id
                    WHERE ref_ct.id IS NULL
                    UNION
                    SELECT DISTINCT ct.owner_type, ct.owner_id
                    FROM candidate_tran ct
                    JOIN transactions ref ON ref.transfer_src = ct.id OR ref.transfer_des = ct.id
                    LEFT JOIN candidate_tran ref_ct ON ref_ct.id = ref.id
                    WHERE ref_ct.id IS NULL
                )
                SELECT jsonb_agg(jsonb_build_object('owner_type', owner_type, 'owner_id', owner_id))
                FROM blocked_owner
            ),
            user_name,
            notify_admin := false
        );

DELETE FROM tmp_endday_archive_reg_ids r
        USING tmp_endday_archive_folio_ids f
        WHERE f.owner_type = 'R'
          AND f.owner_id = r.register_id
          AND EXISTS (
              WITH candidate_tran AS (
                  SELECT tf.owner_type, tf.owner_id, t.id, t.transfer_src, t.transfer_des
                  FROM tmp_endday_archive_folio_ids tf
                  JOIN transactions t ON t.folio_id = tf.folio_id
              ),
              blocked_owner AS (
                  SELECT DISTINCT ct.owner_type, ct.owner_id
                  FROM candidate_tran ct
                  JOIN transactions ref ON ref.id IN (ct.transfer_src, ct.transfer_des)
                  LEFT JOIN candidate_tran ref_ct ON ref_ct.id = ref.id
                  WHERE ref_ct.id IS NULL
                  UNION
                  SELECT DISTINCT ct.owner_type, ct.owner_id
                  FROM candidate_tran ct
                  JOIN transactions ref ON ref.transfer_src = ct.id OR ref.transfer_des = ct.id
                  LEFT JOIN candidate_tran ref_ct ON ref_ct.id = ref.id
                  WHERE ref_ct.id IS NULL
              )
              SELECT 1 FROM blocked_owner bo
              WHERE bo.owner_type = 'R'
                AND bo.owner_id = r.register_id
          );

DELETE FROM tmp_endday_archive_booking_ids b
        USING tmp_endday_archive_folio_ids f
        WHERE f.owner_type = 'B'
          AND f.owner_id = b.booking_id
          AND EXISTS (
              WITH candidate_tran AS (
                  SELECT tf.owner_type, tf.owner_id, t.id, t.transfer_src, t.transfer_des
                  FROM tmp_endday_archive_folio_ids tf
                  JOIN transactions t ON t.folio_id = tf.folio_id
              ),
              blocked_owner AS (
                  SELECT DISTINCT ct.owner_type, ct.owner_id
                  FROM candidate_tran ct
                  JOIN transactions ref ON ref.id IN (ct.transfer_src, ct.transfer_des)
                  LEFT JOIN candidate_tran ref_ct ON ref_ct.id = ref.id
                  WHERE ref_ct.id IS NULL
                  UNION
                  SELECT DISTINCT ct.owner_type, ct.owner_id
                  FROM candidate_tran ct
                  JOIN transactions ref ON ref.transfer_src = ct.id OR ref.transfer_des = ct.id
                  LEFT JOIN candidate_tran ref_ct ON ref_ct.id = ref.id
                  WHERE ref_ct.id IS NULL
              )
              SELECT 1 FROM blocked_owner bo
              WHERE bo.owner_type = 'B'
                AND bo.owner_id = b.booking_id
          );

DELETE FROM tmp_endday_archive_folio_ids f
        WHERE EXISTS (
            WITH candidate_tran AS (
                SELECT tf.owner_type, tf.owner_id, t.id, t.transfer_src, t.transfer_des
                FROM tmp_endday_archive_folio_ids tf
                JOIN transactions t ON t.folio_id = tf.folio_id
            ),
            blocked_owner AS (
                SELECT DISTINCT ct.owner_type, ct.owner_id
                FROM candidate_tran ct
                JOIN transactions ref ON ref.id IN (ct.transfer_src, ct.transfer_des)
                LEFT JOIN candidate_tran ref_ct ON ref_ct.id = ref.id
                WHERE ref_ct.id IS NULL
                UNION
                SELECT DISTINCT ct.owner_type, ct.owner_id
                FROM candidate_tran ct
                JOIN transactions ref ON ref.transfer_src = ct.id OR ref.transfer_des = ct.id
                LEFT JOIN candidate_tran ref_ct ON ref_ct.id = ref.id
                WHERE ref_ct.id IS NULL
            )
            SELECT 1 FROM blocked_owner bo
            WHERE bo.owner_type = f.owner_type
              AND bo.owner_id = f.owner_id
        );
    END IF;

DELETE FROM tmp_endday_archive_booking_ids b
    WHERE EXISTS (
        SELECT 1
        FROM registration rg
        WHERE rg.booking_id = b.booking_id
          AND NOT EXISTS (
              SELECT 1
              FROM tmp_endday_archive_reg_ids tr
              WHERE tr.register_id = rg.id
          )
    );

DELETE FROM tmp_endday_archive_folio_ids f
    WHERE (f.owner_type = 'R' AND NOT EXISTS (
              SELECT 1 FROM tmp_endday_archive_reg_ids r WHERE r.register_id = f.owner_id
          ))
       OR (f.owner_type = 'B' AND NOT EXISTS (
              SELECT 1 FROM tmp_endday_archive_booking_ids b WHERE b.booking_id = f.owner_id
          ));

IF EXISTS (SELECT 1 FROM tmp_endday_archive_folio_ids)
       OR EXISTS (SELECT 1 FROM tmp_endday_archive_reg_ids)
       OR EXISTS (SELECT 1 FROM tmp_endday_archive_booking_ids) THEN
        archive_batch_rows := 0;
        BEGIN
            INSERT INTO backup.folio
            SELECT tf.date_ref, f.*
            FROM folio f
            JOIN tmp_endday_archive_folio_ids tf ON tf.folio_id = f.id
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

INSERT INTO backup.folio_items (date_ref, item_id, folio_id, folio_seq, folio_name, closed, "locked",
                balance, charge_to_folio, note, close_date, close_time, close_user, close_shift,
                vat_nonvat_amt, vat_effect_amt, vat_issued_amt, vat_id)
            SELECT tf.date_ref, fi.item_id, fi.folio_id, fi.folio_seq, fi.folio_name, fi.closed, fi."locked",
                fi.balance, fi.charge_to_folio, fi.note, fi.close_date, fi.close_time, fi.close_user,
                fi.close_shift, fi.vat_nonvat_amt, fi.vat_effect_amt, fi.vat_issued_amt, fi.vat_id
            FROM public.folio_items fi
            JOIN tmp_endday_archive_folio_ids tf ON tf.folio_id = fi.folio_id
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

INSERT INTO backup.folio_details
            SELECT tf.date_ref, fd.*
            FROM folio_details fd
            JOIN tmp_endday_archive_folio_ids tf ON tf.folio_id = fd.folio_id
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

INSERT INTO backup.transactions(date_ref, id, folio_item, post_type, post_date, post_time, tran_type, dept_id,
                description, qty, amount, reference, remark, post_user, post_shift, post_source, card_id, voided, splitted,
                splitted_src, transfer_in, transfer_src, transfer_out, transfer_des, original_folio_id, post_flag, tax_code,
                itemizers, charge_date, active, total_amount, acc_qty, acc_amount, folio_id)
            SELECT tf.date_ref, t.id, t.folio_item, t.post_type, t.post_date, t.post_time, t.tran_type, t.dept_id,
                t.description, t.qty, t.amount, t.reference, t.remark, t.post_user, t.post_shift, t.post_source, t.card_id, t.voided, t.splitted,
                t.splitted_src, t.transfer_in, t.transfer_src, t.transfer_out, t.transfer_des, t.original_folio_id, t.post_flag, t.tax_code,
                t.itemizers, t.charge_date, t.active, t.total_amount, t.acc_qty, t.acc_amount, t.folio_id
            FROM transactions t
            JOIN tmp_endday_archive_folio_ids tf ON tf.folio_id = t.folio_id
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

INSERT INTO backup.cash_sale
            SELECT tf.date_ref, cs.*
            FROM cash_sale cs
            JOIN tmp_endday_archive_folio_ids tf ON tf.folio_id = cs.folio_id
            WHERE tf.owner_type = 'C'
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

INSERT INTO backup.cashier_log
            SELECT tf.date_ref, lg.*
            FROM cashier_log lg
            JOIN tmp_endday_archive_folio_ids tf ON tf.folio_id = lg.folio_id
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

INSERT INTO backup.registration (
                date_ref, id, status, booking_id, booking_item, room_id, guest_id, group_id, arrival, departure, departure_time, walk_in, adult, child, infant,
                extra_adult, extra_child, extra_infant, approved, approve_user, approve_time, checkin_user, checkin_time, checkout_user, checkout_time,
                member_code, channel_id, origin_id, arrive_by, arrive_from, depart_by, depart_to, folio_pattern_id, voucher_no, promo_code, payment_method,
                sales_id, market_id, stay_type, folio_id, credit_limit, note, accno, msg_count, flag_count, rateplan_id, room_posted, charge_to_booking,
                promotion_id, rate_id, modify_user, modify_time, house_used, shared_register_id, share_room, day_used, nights, join_register_id, is_join_room,
                is_group, early_checkout, rsvtype_id, pax, early_checkout_date, mealtype_id, room_charge, other_charge, extra_charge, total_deposit)
            SELECT tr.date_ref, rg.id, rg.status, rg.booking_id, rg.booking_item, rg.room_id, rg.guest_id, rg.group_id, rg.arrival, rg.departure,
                rg.departure_time, rg.walk_in, rg.adult, rg.child, rg.infant, rg.extra_adult, rg.extra_child, rg.extra_infant, rg.approved,
                rg.approve_user, rg.approve_time, rg.checkin_user, rg.checkin_time, rg.checkout_user, rg.checkout_time, rg.member_code, rg.channel_id,
                rg.origin_id, rg.arrive_by, rg.arrive_from, rg.depart_by, rg.depart_to, rg.folio_pattern_id, rg.voucher_no, rg.promo_code,
                rg.payment_method, rg.sales_id, rg.market_id, rg.stay_type, rg.folio_id, rg.credit_limit, rg.note, rg.accno, rg.msg_count, rg.flag_count,
                rg.rateplan_id, rg.room_posted, rg.charge_to_booking, rg.promotion_id, rg.rate_id, rg.modify_user, rg.modify_time, rg.house_used,
                rg.shared_register_id, rg.share_room, rg.day_used, rg.nights, rg.join_register_id, rg.is_join_room, rg.is_group, rg.early_checkout,
                rg.rsvtype_id, rg.pax, rg.early_checkout_date, rg.mealtype_id, rg.room_charge, rg.other_charge, rg.extra_charge, rg.total_deposit
            FROM registration rg
            JOIN tmp_endday_archive_reg_ids tr ON tr.register_id = rg.id
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

INSERT INTO backup.register_guests
            SELECT tr.date_ref, rg.*
            FROM register_guests rg
            JOIN tmp_endday_archive_reg_ids tr ON tr.register_id = rg.register_id
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

INSERT INTO backup.registration_rates
            SELECT tr.date_ref, rr.*
            FROM registration_rates rr
            JOIN tmp_endday_archive_reg_ids tr ON tr.register_id = rr.register_id
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

INSERT INTO backup.guest_request
            SELECT tr.date_ref, gr.*
            FROM guest_request gr
            JOIN tmp_endday_archive_reg_ids tr ON tr.register_id = gr.register_id
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

INSERT INTO backup.deposit
            SELECT tr.date_ref, d.*
            FROM deposit d
            JOIN tmp_endday_archive_reg_ids tr ON tr.register_id = d.register_id
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

INSERT INTO backup.registration_log
            SELECT tr.date_ref, lg.*
            FROM registration_log lg
            JOIN tmp_endday_archive_reg_ids tr ON tr.register_id = lg.register_id
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

INSERT INTO backup.booking (
                 date_ref, id, status, rsvtype_id, book_type, book_by, guest_id, group_id, party_id, member_code, channel_id, origin_id, arrival,
                 arrival_time, arrive_by, arrive_from, departure, departure_time, depart_by, depart_to, folio_pattern_id, voucher_no, promo_code,
                 payment_method, flags, sales_id, market_id, create_user, create_time, modify_user, modify_time, cancel_user, cancel_time, msg_count,
                 flag_count, stay_type, accno, confirm_no, folio_id, credit_limit, confirm_remark, confirm_user, confirm_time, note, book_date,
                 cancel_reason, booking_name, room_charge, other_charge, extra_charge, total_deposit,
                 cancel_date, channel_booking_id, rebook_date, noshow_date, namelist_created)
            SELECT tb.date_ref, b.id, b.status, b.rsvtype_id, b.book_type, b.book_by, b.guest_id, b.group_id, b.party_id, b.member_code,
                 b.channel_id, b.origin_id, b.arrival, b.arrival_time, b.arrive_by, b.arrive_from, b.departure, b.departure_time,
                 b.depart_by, b.depart_to, b.folio_pattern_id, b.voucher_no, b.promo_code, b.payment_method, b.flags, b.sales_id,
                 b.market_id, b.create_user, b.create_time, b.modify_user, b.modify_time, b.cancel_user, b.cancel_time, b.msg_count,
                 b.flag_count, b.stay_type, b.accno, b.confirm_no, b.folio_id, b.credit_limit, b.confirm_remark, b.confirm_user,
                 b.confirm_time, b.note, b.book_date, b.cancel_reason, b.booking_name, b.room_charge, b.other_charge, b.extra_charge, b.total_deposit,
                 b.cancel_date, b.channel_booking_id, b.rebook_date, b.noshow_date, b.namelist_created
            FROM booking b
            JOIN tmp_endday_archive_booking_ids tb ON tb.booking_id = b.id
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

INSERT INTO backup.booking_items (
                date_ref, item_id, booking_id, booking_seq, rateplan_id, roomtype_id, bedtype_id, arrival, departure, qty, adult, child, infant,
                allot_qty, block_qty, inhouse_qty, charge_to, extra_adult, extra_child, extra_infant, no_amend, no_cancel, no_refund,
                room_numbers, rate_id, rooms, promotion_id, noshow_qty, pax, mealtype_id, room_charge, other_charge, extra_charge,
                namelist_group, namelist_orig_seq, namelist_add
            )
            SELECT tb.date_ref, bi.item_id, bi.booking_id, bi.booking_seq, bi.rateplan_id, bi.roomtype_id, bi.bedtype_id, bi.arrival, bi.departure,
                bi.qty, bi.adult, bi.child, bi.infant, bi.allot_qty, bi.block_qty, bi.inhouse_qty, bi.charge_to, bi.extra_adult, bi.extra_child,
                bi.extra_infant, bi.no_amend, bi.no_cancel, bi.no_refund, bi.room_numbers, bi.rate_id, bi.rooms, bi.promotion_id, bi.noshow_qty,
                bi.pax, bi.mealtype_id, bi.room_charge, bi.other_charge, bi.extra_charge, bi.namelist_group, bi.namelist_orig_seq, bi.namelist_add
            FROM booking_items bi
            JOIN tmp_endday_archive_booking_ids tb ON tb.booking_id = bi.booking_id
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

INSERT INTO backup.booking_guests (
                date_ref, booking_id, guest_id, registered, register_id, booking_item, room_seq
            )
            SELECT tb.date_ref, bg.booking_id, bg.guest_id, bg.registered, bg.register_id, bg.booking_item, bg.room_seq
            FROM booking_guests bg
            JOIN tmp_endday_archive_booking_ids tb ON tb.booking_id = bg.booking_id
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

INSERT INTO backup.booking_rates
            SELECT tb.date_ref, br.*
            FROM booking_rates br
            JOIN tmp_endday_archive_booking_ids tb ON tb.booking_id = br.booking_id
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

INSERT INTO backup.guest_request
            SELECT tb.date_ref, gr.*
            FROM guest_request gr
            JOIN tmp_endday_archive_booking_ids tb ON tb.booking_id = gr.booking_id
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

INSERT INTO backup.deposit
            SELECT tb.date_ref, d.*
            FROM deposit d
            JOIN tmp_endday_archive_booking_ids tb ON tb.booking_id = d.booking_id
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

INSERT INTO backup.booking_log
            SELECT tb.date_ref, bl.*
            FROM booking_log bl
            JOIN tmp_endday_archive_booking_ids tb ON tb.booking_id = bl.booking_id
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

UPDATE transactions t
            SET transfer_src = NULL
            WHERE t.folio_id IN (SELECT folio_id FROM tmp_endday_archive_folio_ids)
              AND t.transfer_src IN (
                  SELECT t2.id
                  FROM transactions t2
                  WHERE t2.folio_id IN (SELECT folio_id FROM tmp_endday_archive_folio_ids)
              );

UPDATE transactions t
            SET transfer_des = NULL
            WHERE t.folio_id IN (SELECT folio_id FROM tmp_endday_archive_folio_ids)
              AND t.transfer_des IN (
                  SELECT t2.id
                  FROM transactions t2
                  WHERE t2.folio_id IN (SELECT folio_id FROM tmp_endday_archive_folio_ids)
              );

DELETE FROM folio WHERE id IN (
                SELECT folio_id FROM tmp_endday_archive_folio_ids WHERE owner_type = 'B'
            );
            DELETE FROM registration WHERE id IN (SELECT register_id FROM tmp_endday_archive_reg_ids);
            DELETE FROM booking WHERE id IN (SELECT booking_id FROM tmp_endday_archive_booking_ids);
            DELETE FROM folio WHERE id IN (
                SELECT folio_id FROM tmp_endday_archive_folio_ids WHERE owner_type = 'C'
            );

archived_rows := archived_rows + archive_batch_rows;
        EXCEPTION WHEN OTHERS THEN
            GET STACKED DIAGNOSTICS
                _sqlstate= RETURNED_SQLSTATE,
                _msg_text= MESSAGE_TEXT,
                _detail  = PG_EXCEPTION_DETAIL,
                _hint    = PG_EXCEPTION_HINT,
                _context = PG_EXCEPTION_CONTEXT;
            PERFORM sp_log_error('sp_endday_clear_data', _msg_text, _detail, _sqlstate, _hint, _context, null::jsonb, user_name, notify_admin := false);
        END;
    END IF;

FOR table_name IN (
        SELECT t.table_name::text
        FROM information_schema.TABLES t
        WHERE t.table_schema='backup')
    LOOP
        EXECUTE format('DELETE FROM backup.%I WHERE date_ref <= %L', table_name, v_system_date - v_clearing_days);
        GET DIAGNOSTICS rec_count = ROW_COUNT; deleted_rows = deleted_rows + rec_count;
    END LOOP;

status = 'success';
    msg = format('%s records archived, %s records deleted', archived_rows, deleted_rows);
END