Drop Reversed Invoice in iDempiere

SET search_path TO adempiere;

-- SELECT * FROM fnt_drop_reversed_invoice(1085627);

CREATE OR REPLACE FUNCTION fnt_drop_reversed_invoice(p_invoice_id NUMERIC(10,0))
RETURNS BOOLEAN
AS $$
DECLARE
    v_reversal_id NUMERIC(10,0);
   
    v_allocation_id NUMERIC(10,0);
BEGIN
   
    /*********************************************************/
   
    v_reversal_id =
    (
            SELECT f.reversal_id FROM c_invoice f
            WHERE f.c_invoice_id = p_invoice_id
    );
   
    /*********************************************************/
   
    v_allocation_id =
    (
            SELECT DISTINCT a.c_allocationhdr_id FROM c_allocationhdr AS a
              JOIN c_allocationline al
              ON a.c_allocationhdr_id = al.c_allocationhdr_id
            WHERE al.c_invoice_id = v_reversal_id
    );
   
    DELETE FROM fact_acct
    WHERE fact_acct.ad_table_id = 735
        AND fact_acct.record_id = v_allocation_id;
   
    DELETE FROM c_allocationline
    WHERE c_allocationline.c_allocationhdr_id = v_allocation_id;
   
    DELETE FROM c_allocationhdr
    WHERE c_allocationhdr.c_allocationhdr_id = v_allocation_id;
   
    /*********************************************************/
   
    DELETE FROM fact_acct
    WHERE fact_acct.ad_table_id = 318
        AND fact_acct.record_id = p_invoice_id;
       
    UPDATE c_invoiceline
        SET processed = 'N'
    WHERE c_invoiceline.c_invoice_id = p_invoice_id;
   
    UPDATE c_invoice
        SET docstatus = 'DR',
        docaction = 'CO',
        processed = 'N',
        posted = 'N',
        reversal_id = null
    WHERE c_invoice.c_invoice_id = p_invoice_id;
   
    /*********************************************************/
   
    DELETE FROM fact_acct
    WHERE fact_acct.ad_table_id = 318
        AND fact_acct.record_id = v_reversal_id;
       
    UPDATE c_invoiceline
        SET processed = 'N'
    WHERE c_invoiceline.c_invoice_id = v_reversal_id;
       
    DELETE FROM c_invoiceline
    WHERE c_invoiceline.c_invoice_id = v_reversal_id;
   
    UPDATE c_invoice
        SET docstatus = 'DR',
        docaction = 'CO',
        processed = 'N',
        posted = 'N',
        reversal_id = null
    WHERE c_invoice.c_invoice_id = v_reversal_id;
   
    DELETE FROM c_invoice
    WHERE c_invoice.c_invoice_id = v_reversal_id;
   
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;