CREATE OR REPLACE FUNCTION fix_35726_issue_data()
  RETURNS VOID AS
$BODY$ 
DECLARE
 Cur_inv RECORD;
 v_fin_payment_detail_id VARCHAR(32);
 v_payment_detail_sum NUMERIC;

   
BEGIN
 FOR Cur_inv IN
    (SELECT o.c_order_id, inv.c_invoice_id FROM c_order o
        JOIN c_order_discount od ON od.c_order_id = o.c_order_id
        JOIN c_invoice inv ON inv.c_order_id = o.c_order_id
	JOIN c_paymentterm oterm ON oterm.c_paymentterm_id = o.c_paymentterm_id	
        JOIN c_paymentterm invterm ON invterm.c_paymentterm_id = inv.c_paymentterm_id
     WHERE o.docstatus = 'CO'
      AND inv.totallines > o.totallines
      AND (select count(*) from C_Paymenttermline where c_paymentterm_id = invterm.c_paymentterm_id) > 1
      AND inv.grandtotal <> 
         (select sum(fpsd.amount) from fin_payment_scheduledetail fpsd join fin_payment_schedule fps on fpsd.fin_payment_schedule_invoice = fps.fin_payment_schedule_id 
            where fps.c_invoice_id = inv.c_invoice_id)
     ) 
  LOOP    
    RAISE NOTICE '%','Processing order: ' || Cur_inv.c_order_id || ' with invoice: ' || Cur_inv.c_invoice_id; 

   -- Finding the wrong payment schedule detail
    SELECT fpsd.fin_payment_scheduledetail_id INTO v_fin_payment_detail_id FROM fin_payment_scheduledetail fpsd
      JOIN fin_payment_schedule fps ON fpsd.fin_payment_schedule_invoice = fps.fin_payment_schedule_id 
    WHERE fpsd.amount > fps.amount 
      AND fps.c_invoice_id = Cur_inv.c_invoice_id;

    -- Get Payment schedule detail total payment
    SELECT SUM(fpsd.amount) INTO v_payment_detail_sum FROM fin_payment_scheduledetail fpsd
      JOIN fin_payment_schedule fps on fpsd.fin_payment_schedule_invoice = fps.fin_payment_schedule_id 
    WHERE fpsd.fin_payment_scheduledetail_id <> v_fin_payment_detail_id
      AND fps.c_invoice_id = Cur_inv.c_invoice_id;

    --Update the payment schedule detail with the correct amount 
    UPDATE fin_payment_scheduledetail fpsd
     SET amount = (SELECT grandtotal from c_invoice where c_invoice_id = Cur_inv.c_invoice_id) - v_payment_detail_sum
    WHERE fpsd.fin_payment_scheduledetail_id = v_fin_payment_detail_id;   
      
  END LOOP;
END;   $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;


--Run the script
SELECT fix_35726_issue_data();

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