This script will delete iexpense report from backend, also it will realesed credit card (if any) transaction related to this expense report. So we can create new expense report against that credit card transaction.
Script:
Script:
DECLARE
P_ReportID NUMBER := &report_header_id;
l_TempReportHeaderID NUMBER;
l_TempReportLineID NUMBER;
l_childItemKeySeq NUMBER;
l_wf_active BOOLEAN := FALSE;
l_wf_exist BOOLEAN := FALSE;
l_end_date wf_items.end_date%TYPE;
l_child_item_key varchar2(2000);
CURSOR ReportLines IS
(SELECT REPORT_HEADER_ID, REPORT_LINE_ID FROM AP_EXPENSE_REPORT_LINES_ALL WHERE REPORT_HEADER_ID = P_ReportID);
BEGIN
DBMS_OUTPUT.PUT_LINE('Start Deleting Report ' || P_ReportID);
DBMS_OUTPUT.PUT('Delete Distributions - ');
DELETE FROM AP_EXP_REPORT_DISTS_ALL WHERE REPORT_HEADER_ID = P_ReportID;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
DBMS_OUTPUT.PUT('Delete Attendees - ');
DELETE FROM OIE_ATTENDEES_ALL oat WHERE oat.REPORT_LINE_ID IN ( SELECT REPORT_LINE_ID FROM ap_expense_report_lines_all WHERE report_header_id = P_ReportID);
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
DBMS_OUTPUT.PUT('Delete Add on Mileage Rates - ');
DELETE FROM OIE_ADDON_MILEAGE_RATES addon WHERE addon.REPORT_LINE_ID IN ( SELECT REPORT_LINE_ID FROM ap_expense_report_lines_all WHERE report_header_id = P_ReportID);
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
DBMS_OUTPUT.PUT('Delete Perdiem Daily Breakup - ');
DELETE FROM OIE_PDM_DAILY_BREAKUPS db WHERE db.REPORT_LINE_ID IN ( SELECT REPORT_LINE_ID FROM ap_expense_report_lines_all WHERE report_header_id = P_ReportID);
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
DBMS_OUTPUT.PUT('Delete Perdiem Destinations - ');
DELETE FROM OIE_PDM_DESTINATIONS db WHERE db.REPORT_LINE_ID IN ( SELECT REPORT_LINE_ID FROM ap_expense_report_lines_all WHERE report_header_id = P_ReportID);
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
DBMS_OUTPUT.PUT('Delete Policy Violations - ');
DELETE FROM AP_POL_VIOLATIONS_ALL WHERE REPORT_HEADER_ID = P_ReportID;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
DBMS_OUTPUT.PUT('Update CC transactions, make them available for future reports - ');
UPDATE AP_CREDIT_CARD_TRXNS_ALL SET REPORT_HEADER_ID = NULL, EXPENSED_AMOUNT = 0 WHERE REPORT_HEADER_ID = P_ReportID;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
OPEN ReportLines;
DBMS_OUTPUT.PUT_LINE('Delete Attachments');
LOOP
FETCH ReportLines into l_TempReportHeaderID, l_TempReportLineID;
EXIT WHEN ReportLines%NOTFOUND;
/* Delete attachments assocated with the line */
fnd_attached_documents2_pkg.delete_attachments(
X_entity_name => 'OIE_LINE_ATTACHMENTS',
X_pk1_value => l_TempReportLineID,
X_delete_document_flag => 'Y'
);
END LOOP;
CLOSE ReportLines;
DBMS_OUTPUT.PUT('Delete Report Lines - ');
DELETE FROM ap_expense_report_lines_all WHERE report_header_id = P_ReportID;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
AP_WEB_NOTES_PKG.DeleteERNotes (
p_src_report_header_id => P_ReportID
);
fnd_attached_documents2_pkg.delete_attachments(
X_entity_name => 'OIE_HEADER_ATTACHMENTS',
X_pk1_value => P_ReportID,
X_delete_document_flag => 'Y'
);
DBMS_OUTPUT.PUT('Delete Report - ');
DELETE FROM ap_expense_report_headers_all WHERE report_header_id = P_ReportID;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
DBMS_OUTPUT.PUT_LINE('Stop and purge all workflows');
begin
select end_date
into l_end_date
from wf_items
where item_type = 'APEXP'
and item_key = to_char(P_ReportID);
if l_end_date is NULL then
l_wf_active := TRUE;
else
l_wf_active := FALSE;
end if;
l_wf_exist := TRUE;
exception
when no_data_found then
l_wf_active := FALSE;
l_wf_exist := FALSE;
end;
IF l_wf_exist THEN
IF l_wf_active THEN
wf_engine.AbortProcess (itemtype => 'APEXP',
itemkey => to_char(P_ReportID),
cascade => TRUE);
END IF;
begin
l_childItemKeySeq := WF_ENGINE.GetItemAttrNumber('APEXP',
P_ReportID,
'AME_CHILD_ITEM_KEY_SEQ');
exception
when others then
if (wf_core.error_name = 'WFENG_ITEM_ATTR') then
l_childItemKeySeq := 0;
else
raise;
end if;
end;
IF (l_childItemKeySeq IS NOT NULL AND l_childItemKeySeq > 0) THEN
FOR i in 1 .. l_childItemKeySeq LOOP
l_child_item_key := to_char(P_ReportID) || '-' || to_char(i);
begin
select end_date
into l_end_date
from wf_items
where item_type = 'APEXP'
and item_key = l_child_item_key;
if l_end_date is NULL then
l_wf_active := TRUE;
else
l_wf_active := FALSE;
end if;
l_wf_exist := TRUE;
exception
when no_data_found then
l_wf_active := FALSE;
l_wf_exist := FALSE;
end;
IF (l_wf_exist) THEN
IF l_wf_active THEN
wf_engine.AbortProcess (itemtype => 'APEXP',
itemkey => l_child_item_key,
cascade => TRUE);
END IF;
wf_purge.Items(itemtype => 'APEXP',
itemkey => l_child_item_key);
wf_purge.TotalPerm(itemtype => 'APEXP',
itemkey => l_child_item_key,
runtimeonly => TRUE);
END IF;
END LOOP;
END IF;
wf_purge.Items(itemtype => 'APEXP',
itemkey => to_char(P_ReportID));
wf_purge.TotalPerm(itemtype => 'APEXP',
itemkey => to_char(P_ReportID),
runtimeonly => TRUE);
END IF;
DBMS_OUTPUT.PUT_LINE('Done Deleting Report ' || P_ReportID);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
Dbms_Output.put_line(SQLERRM);
END;
