--Function to fix all erroneous data caused by issue 34992
--Find all corrupt invoices and then fixes iterating them
CREATE OR REPLACE FUNCTION fix_invoice_data()
  RETURNS VOID AS
$BODY$ 
DECLARE
 Cur_inv RECORD;

 v_payment_id VARCHAR(32);
 v_dummy_payment_id VARCHAR(32);
 v_dummy_payment_detail_id VARCHAR(32);
 v_dummy_payment_schedule_id VARCHAR(32);
 v_dummy_payment_scheduledetail_id VARCHAR(32);
 v_rev_payment_detail_id VARCHAR(32);
 v_rev_payment_schedule_id VARCHAR(32);
 v_rev_payment_scheduledetail_id VARCHAR(32);
 v_PaymentDocType_ID VARCHAR(32);
 v_PaymentDocumentNo VARCHAR(30);
BEGIN
 FOR Cur_inv IN
    (SELECT i.*, rev.c_invoice_id AS rev_invoice_id, rev.grandtotal AS rev_grandtotal, rev.documentno AS rev_documentno, 
      rev.dateinvoiced AS rev_dateinvoiced
     FROM c_invoice i
       JOIN c_invoice_reverse ir ON i.c_invoice_id = ir.reversed_c_invoice_id
       JOIN c_invoice rev ON ir.c_invoice_id = rev.c_invoice_id
       LEFT JOIN fin_payment_schedule fps ON fps.c_invoice_id = i.c_invoice_id
     WHERE
       i.docstatus = 'VO' AND
       fps.c_invoice_id IS NULL AND
       EXISTS (SELECT 1
                FROM  c_invoiceline il 
                 JOIN c_orderline ol ON ol.c_orderline_id = il.c_orderline_id 
                 JOIN c_order o ON ol.c_order_id = o.c_order_id 
                 JOIN fin_payment_schedule fps ON fps.c_order_id = o.c_order_id
                 JOIN fin_payment_scheduledetail fpsd ON fps.fin_payment_schedule_id = fpsd.fin_payment_schedule_order 
                 JOIN fin_payment_detail fpd ON fpd.fin_payment_detail_id = fpsd.fin_payment_detail_id
                 JOIN fin_payment fp ON fp.fin_payment_id = fpd.fin_payment_id
                WHERE i.c_invoice_id = il.c_invoice_id)) 
  LOOP
    --Fix the current invoice and it reversed
    RAISE NOTICE '%','Processing invoice: ' || Cur_inv.c_invoice_id || ' and it reversed: ' || Cur_inv.rev_invoice_id;

    v_dummy_payment_id := get_uuid();
    v_dummy_payment_detail_id := get_uuid(); 
    v_dummy_payment_schedule_id := get_uuid();
    v_dummy_payment_scheduledetail_id := get_uuid(); 
    v_rev_payment_detail_id := get_uuid();
    v_rev_payment_schedule_id := get_uuid();
    v_rev_payment_scheduledetail_id := get_uuid(); 

    --Finding related order payment id
    SELECT fp.fin_payment_id INTO v_payment_id
      FROM  c_invoiceline il 
       JOIN c_orderline ol ON ol.c_orderline_id = il.c_orderline_id 
       JOIN c_order o ON ol.c_order_id = o.c_order_id 
       JOIN fin_payment_schedule fps ON fps.c_order_id = o.c_order_id
       JOIN fin_payment_scheduledetail fpsd ON fps.fin_payment_schedule_id = fpsd.fin_payment_schedule_order 
       JOIN fin_payment_detail fpd ON fpd.fin_payment_detail_id = fpsd.fin_payment_detail_id
       JOIN fin_payment fp ON fp.fin_payment_id = fpd.fin_payment_id
    WHERE il.c_invoice_id = Cur_inv.c_invoice_id;

    IF(v_payment_id IS NULL) THEN
     RAISE NOTICE '%', 'ORDER PAYMENT NOT FOUND';
     RETURN;
    END IF;

    --Get the payment documentno
    v_PaymentDocType_ID := AD_GET_DOCTYPE(Cur_inv.ad_client_id, Cur_inv.ad_org_id, CASE WHEN Cur_inv.issotrx='Y' THEN 'ARR' ELSE 'APP' END);
    SELECT * INTO v_PaymentDocumentNo FROM AD_Sequence_Doctype(v_PaymentDocType_ID, Cur_inv.ad_client_id, 'Y') ;
    IF (v_PaymentDocumentNo IS NULL) THEN
     SELECT * INTO v_PaymentDocumentNo FROM AD_Sequence_Doc('DocumentNo_FIN_Payment', Cur_inv.ad_client_id, 'Y') ;
    END IF;  

    v_PaymentDocumentNo := v_PaymentDocumentNo || '*Z*';

    --Creating a dummy payment
    INSERT INTO fin_payment(
                fin_payment_id, ad_client_id, ad_org_id, 
                created, createdby, updated, updatedby, 
                isactive, isreceipt, c_bpartner_id, paymentdate,
                c_currency_id, amount, writeoffamt,
                finacc_txn_amount, finacc_txn_convert_rate, fin_paymentmethod_id,                 
                status, documentno,
                processed, processing, posted, 
                description,
                fin_financial_account_id, c_doctype_id, c_project_id, c_campaign_id, c_costcenter_id,
                c_activity_id, user1_id, user2_id, em_aprm_process_payment, em_aprm_reconcile_payment,
                em_aprm_add_scheduledpayments)
    SELECT 
                v_dummy_payment_id, Cur_inv.ad_client_id, Cur_inv.ad_org_id, 
                now(), '100', now(), '100', 
                fp.isactive, fp.isreceipt, fp.c_bpartner_id, Cur_inv.rev_dateinvoiced,
                fp.c_currency_id, 0, 0,
                fp.finacc_txn_amount, fp.finacc_txn_convert_rate, fp.fin_paymentmethod_id,                 
                fp.status, v_PaymentDocumentNo,
                'N', 'N', 'N', 
                'Invoice No.: ' || Cur_inv.rev_documentno || ', ' || Cur_inv.documentno,                
                fp.fin_financial_account_id, fp.c_doctype_id, fp.c_project_id, fp.c_campaign_id, fp.c_costcenter_id,
                fp.c_activity_id, fp.user1_id, fp.user2_id, fp.em_aprm_process_payment, fp.em_aprm_reconcile_payment,
                fp.em_aprm_add_scheduledpayments 
    FROM fin_payment fp          
    WHERE fp.fin_payment_id = v_payment_id;

    --Creating a payment detail
    INSERT INTO fin_payment_detail(
                 fin_payment_detail_id, ad_client_id, ad_org_id, 
                 created, createdby, updated, updatedby, 
                 fin_payment_id, amount, refund, isactive,
                 writeoffamt, c_glitem_id, isprepayment)
        VALUES   (v_dummy_payment_detail_id, Cur_inv.ad_client_id, Cur_inv.ad_org_id, 
                 now(), '100', now(), '100', 
                 v_dummy_payment_id, Cur_inv.grandtotal, 'N', 'Y',
                 0, NULL, 'N');

    --Creating the invoice payment schedule
    INSERT INTO fin_payment_schedule(
                 fin_payment_schedule_id, ad_client_id, ad_org_id, isactive,
                 created, createdby, updated, updatedby,
                 c_invoice_id, c_currency_id, c_order_id,
                 fin_paymentmethod_id, amount, paidamt, duedate, expecteddate,
                 outstandingamt, fin_payment_priority_id) 
        VALUES   (v_dummy_payment_schedule_id, Cur_inv.ad_client_id, Cur_inv.ad_org_id, 'Y',
                 now(), '100', now(), '100',
                 Cur_inv.c_invoice_id, Cur_inv.c_currency_id, null,
                 Cur_inv.fin_paymentmethod_id, Cur_inv.grandtotal, Cur_inv.grandtotal , Cur_inv.dateinvoiced, Cur_inv.dateinvoiced,
                 0, null);

    --Creating the invoice schedule detail
    INSERT INTO fin_payment_scheduledetail(
                 fin_payment_scheduledetail_id, ad_client_id, ad_org_id, isactive,
                 created, createdby, updated, updatedby,
                 fin_payment_schedule_order,
                 fin_payment_schedule_invoice,
                 fin_payment_detail_id,
                 amount, isinvoicepaid,
                 c_bpartner_id)
        VALUES   (v_dummy_payment_scheduledetail_id, Cur_inv.ad_client_id, Cur_inv.ad_org_id, 'Y',
                 now(), '100', now(), '100',
                 null,
                 v_dummy_payment_schedule_id,
                 v_dummy_payment_detail_id,
                 Cur_inv.grandtotal, 'Y',
                 Cur_inv.c_bpartner_id);

    --Creating the reverse invoice payment detail
    INSERT INTO fin_payment_detail(
                 fin_payment_detail_id, ad_client_id, ad_org_id, 
                 created, createdby,updated, updatedby, 
                 fin_payment_id, amount, refund, isactive,
                 writeoffamt, c_glitem_id, isprepayment)
        VALUES   (v_rev_payment_detail_id, Cur_inv.ad_client_id, Cur_inv.ad_org_id, 
                 now(), '100', now(), '100',
                 v_dummy_payment_id, Cur_inv.rev_grandtotal, 'N', 'Y',
                 0, NULL, 'N');

    --Creating the reverse invoice payment schedule
    INSERT INTO fin_payment_schedule(
                 fin_payment_schedule_id, ad_client_id, ad_org_id, isactive,
                 created, createdby, updated, updatedby,
                 c_invoice_id, c_currency_id, c_order_id,
                 fin_paymentmethod_id, 
                 amount, 
                 paidamt,
                 duedate, expecteddate,
                 outstandingamt, fin_payment_priority_id) 
        VALUES   (v_rev_payment_schedule_id, Cur_inv.ad_client_id, Cur_inv.ad_org_id, 'Y',
                 now(), '100', now(), '100',
                 Cur_inv.rev_invoice_id, Cur_inv.c_currency_id, null,
                 Cur_inv.fin_paymentmethod_id,
                 Cur_inv.rev_grandtotal,
                 Cur_inv.rev_grandtotal, Cur_inv.rev_dateinvoiced, Cur_inv.rev_dateinvoiced,
                 0, null);

    --Creating the reverse invoice schedule detail
    INSERT INTO fin_payment_scheduledetail(
                 fin_payment_scheduledetail_id, ad_client_id, ad_org_id, isactive,
                 created, createdby, updated, updatedby,
                 fin_payment_schedule_order,
                 fin_payment_schedule_invoice,
                 fin_payment_detail_id,
                 amount, isinvoicepaid, 
                 c_bpartner_id)
     VALUES      (v_rev_payment_scheduledetail_id, Cur_inv.ad_client_id, Cur_inv.ad_org_id, 'Y',
                 now(), '100', now(), '100',
                 null,
                 v_rev_payment_schedule_id,
                 v_rev_payment_detail_id,
                 Cur_inv.rev_grandtotal, 'Y',
                 Cur_inv.c_bpartner_id);

    --Set payment values
    UPDATE fin_payment
     SET processed = 'Y',
      status = 'RPR',
      finacc_txn_amount = 0,
      em_aprm_executepayment = 'N'
    WHERE fin_payment_id = v_dummy_payment_id; 
  
    --Set original invoice prepaymentamt = '0'
    UPDATE c_invoice
     SET prepaymentamt = 0
    WHERE c_invoice_id = Cur_inv.c_invoice_id;  
  
    --Mark the reverse invoice as paid, prepaymentamt = 0, outstandingamt = 0,
    --daystilldue = 0 and totalpaid = grandtotal
    UPDATE c_invoice
     SET ispaid = 'Y',
      prepaymentamt = 0,
      outstandingamt = 0,
      daystilldue = '0',
      totalpaid = grandtotal
    WHERE c_invoice_id = Cur_inv.rev_invoice_id;     
  END LOOP;
END;   $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;


--Run the script
SELECT fix_invoice_data();

--Remove the created function as it will not be used anymore
DROP FUNCTION fix_invoice_data();