Expense report ORPHAN Record deletion with Script
Sometimes, we can see that the expense report can have some orphan events and this is system bug which will happen very rarely.. here in this case, we had faced an issue where we can not see the expense report in the ap_expense_report_headers_all where we have the data in the lines and distributions table.. since it is not having the header record, it became as an orphan record and not allowing the user to select any credit card transactions in the new expense report.. then we verified the same in ap_expense_report_lines_all and ap_expense_report_distributions_all and found that the data was not deleted from these tables and which is causing an issue now… so we have used the below query to back up the table data and excuted to clear the orphan events.. which was resolved our issue..
Take Back up from the below Queries:
============================
create table ap_expense_dist_bkp as
select * from ap_exp_report_dists_all aed where aed.report_header_id = &1;
create table ap_expense_line_bkp as
select * from ap_expense_report_lines_all aerl where aerl.report_header_id =&1;
Execute the below Query and confirm 1 row selected:
======================================
select * from ap_exp_report_dists_all aed where report_header_id= 350007 AND report_distribution_id =’1304109′ AND report_line_id = ‘2887776’
—1 row selected —-
Execute Below Query to Delete the Record:
=================================
DELETE FROM ap_exp_report_dists_all WHERE report_header_id= 350007 AND report_distribution_id =’1304109′ AND report_line_id = ‘2887776’;
–Validation: Run this query by providing report header id as parameter
SELECT report_header_id, CASE WHEN total = (SELECT NVL(SUM(amount),0) FROM ap_expense_report_lines_all WHERE report_header_id = :1 AND (itemization_parent_id IS NULL OR itemization_parent_id <> -1)) AND
total = amt_due_ccard_company + amt_due_employee AND
total = (SELECT NVL(SUM(amount),0) FROM ap_exp_report_dists_all WHERE report_header_id = :1)
THEN ‘Totals match’
ELSE ‘Totals do not match’
END AS “Result”
FROM ap_expense_report_headers_all WHERE report_header_id = :1;
— Replace : 1 with report header id.
— If validation is successful, we should see “Totals match” in Result column
COMMIT;
