In business operations, it’s not uncommon to encounter situations where bulk actions are required, such as deleting thousands of invoices due to incorrect entries. Manually updating and deleting each invoice would be time-consuming and prone to errors. To streamline this process, I developed a Python script that automates setting the status of invoices to “Incomplete” and then deleting them using the Oracle Fusion REST API. 

Business Scenario 

Recently, we encountered a scenario where a large batch of auto-generated invoices contained incorrect entries. To correct this, we needed to delete thousands of invoices. However, Oracle Fusion requires invoices to be marked as “Incomplete” before they can be deleted. Performing this task manually for each invoice would have taken an immense amount of time. Instead, I created a Python script to automate the entire process, saving both time and effort. 

Exploring a career path in Python?
Get started with our FREE Python Training

Step 1: Setting Invoices to “Incomplete”

The first step in the process is to update the status of each invoice to “Incomplete.” This ensures that the invoices are eligible for deletion. Here is the Python code that performs this task. 

import requests 
from requests.auth import HTTPBasicAuth 
import json 
import os 

# Base URL for Oracle REST API 
base_url = "https://etit.fa.em2.com/fscmRestApi/resources/11.13.18.05/receivablesInvoices/" 

# List of invoices to update 
invoices_to_update = [ 
    {"CustomerTransactionId": "331611"}, 
    {"CustomerTransactionId": "333475"} 
] 

# Oracle Fusion credentials retrieved from environment variables 
username = os.getenv("ORACLE_FUSION_USERNAME") 
password = os.getenv("ORACLE_FUSION_PASSWORD") 

# Loop through each invoice and update its status to "Incomplete" 
for invoice in invoices_to_update: 
    customer_transaction_id = invoice.pop("CustomerTransactionId") 
    update_url = f"{base_url}/{customer_transaction_id}" 

    # Define the payload to update InvoiceStatus to "Incomplete" 
    payload = { 
        "InvoiceStatus": "Incomplete" 
    } 

    # Perform the PATCH request with basic authentication 
    response = requests.patch(update_url, json=payload, auth=HTTPBasicAuth(username, password)) 

    # Check if the request was successful and print the response data 
    if response.status_code == 200: 
        print(f"Invoice {customer_transaction_id} updated to 'Incomplete' successfully.") 
    else: 
        print(f"Failed to update invoice {customer_transaction_id}. Status code: {response.status_code}") 

Step 2: Deleting Invoices After Setting Them to “Incomplete” 

Once the invoices are marked as “Incomplete,” they are ready to be deleted. The following Python code automates the deletion process. 

import requests 
from requests.auth import HTTPBasicAuth 
import os 

# Base URL for Oracle REST API 
base_url = "https://etit.fa.em2.com/fscmRestApi/resources/11.13.18.05/receivablesInvoices/" 

# List of invoices to delete 
invoices_to_delete = [ 
    {"CustomerTransactionId": "331611"}, 
    {"CustomerTransactionId": "333475"} 
] 

# Oracle Fusion Cloud credentials retrieved from environment variables 
username = os.getenv("ORACLE_FUSION_USERNAME") 
password = os.getenv("ORACLE_FUSION_PASSWORD") 

# Loop through each invoice and send a DELETE request 
for invoice in invoices_to_delete: 
    invoice_id = invoice["CustomerTransactionId"] 
    url = f"{base_url}/{invoice_id}" 

    response = requests.delete(url, auth=HTTPBasicAuth(username, password)) 

    if response.status_code == 204:  # No Content, successful deletion 
        print(f"Invoice {invoice_id} deleted successfully.") 
    else: 
        print(f"Failed to delete Invoice {invoice_id}. Status code: {response.status_code}, Response: {response.text}") 

Retrieving Customer Transaction IDs 

To identify the invoices that needed to be deleted, I used the following SQL query. This query retrieves the CustomerTransactionId for the specific transaction numbers you wish to delete. The results can then be exported and formatted in the Python script. 

SELECT trx_number, 
       rct.customer_trx_id, 
       batch.name, 
       '{"CustomerTransactionId": "' || CUSTOMER_TRX_ID || '"},' AS json_format 
FROM RA_CUSTOMER_TRX_ALL rct, 
     hr_operating_units hou, 
     ra_batches_all batch 
WHERE 1=1  
  AND rct.org_id = hou.organization_id 
  AND batch.batch_id = rct.batch_id 
  AND rct.trx_number IN ('9200000012', '9200000013') 
  AND hou.name = :bu_name 
-- AND batch.name = :batch_name  -- Uncomment if batch name filtering is required 

To apply this SQL query, you can develop a BI Publisher (BIP) report and extract the CustomerTransactionId for all the transactions that need to be deleted.

Additionally you can combine step 1 and step 2 into one.  
Combined Python Script for Setting Invoice Status to “Incomplete” and Deleting the Invoice 

import requests 
from requests.auth import HTTPBasicAuth 
import os 
import json 

# Base URL for Oracle REST API 
base_url = "https://etit.fa.em2.com/fscmRestApi/resources/11.13.18.05/receivablesInvoices/" 

# Oracle Fusion credentials retrieved from environment variables 
username = os.getenv("ORACLE_FUSION_USERNAME") 
password = os.getenv("ORACLE_FUSION_PASSWORD") 

def set_status_to_incomplete_and_delete(invoice_id): 
    # Set the invoice status to "Incomplete" 
    update_url = f"{base_url}/{invoice_id}" 
    payload = { 
        "InvoiceStatus": "Incomplete" 
    } 

    # Perform the PATCH request with basic authentication 
    response = requests.patch(update_url, json=payload, auth=HTTPBasicAuth(username, password)) 

    # Check if the request was successful 
    if response.status_code == 200: 
        print(f"Invoice {invoice_id} updated to 'Incomplete' successfully.") 
        # Proceed to delete the invoice 
        delete_invoice(invoice_id) 
    else: 
        print(f"Failed to update invoice {invoice_id}. Status code: {response.status_code}, Response: {response.text}") 

def delete_invoice(invoice_id): 
    # Delete the invoice 
    delete_url = f"{base_url}/{invoice_id}" 

    response = requests.delete(delete_url, auth=HTTPBasicAuth(username, password)) 

    # Check if the deletion was successful 
    if response.status_code == 204:  # No Content, successful deletion 
        print(f"Invoice {invoice_id} deleted successfully.") 
    else: 
        print(f"Failed to delete Invoice {invoice_id}. Status code: {response.status_code}, Response: {response.text}") 

# List of invoices to process (set to "Incomplete" and then delete) 
invoices_to_process = [ 
    {"CustomerTransactionId": "331611"}, 
    {"CustomerTransactionId": "333475"} 
] 

# Loop through each invoice and process it 
for invoice in invoices_to_process: 
    invoice_id = invoice["CustomerTransactionId"] 
    set_status_to_incomplete_and_delete(invoice_id) 

Conclusion 

By automating the process of setting invoice statuses to “Incomplete” and then deleting them, this Python script significantly reduces the time and effort required to correct bulk invoice errors. This approach is particularly useful when dealing with thousands of invoices, ensuring efficiency and accuracy. 

If you have similar scenarios or any other questions, feel free to reach out in the comments! 

This blog post outlines a complete solution for handling bulk invoice deletions using Python and Oracle Fusion REST API, emphasizing the business need that led to the development of the script. 

Author: Kashif Baksh is a Senior Principal Consultant at Fusion Practices