--Function to fix all erroneous data caused by issue 36297
--Find all corrupt PSDs and then fixes iterating them
CREATE OR REPLACE FUNCTION fix_psd_data()
  RETURNS VOID AS
$BODY$ 
DECLARE
 Cur_PSD RECORD;
BEGIN
 FOR Cur_PSD IN (
    SELECT fpsd.fin_payment_scheduledetail_id, i.documentno as invdocumentno, fps.amount as InvoicePSAmount
	FROM  
	  c_invoice i 
	  JOIN c_invoiceline il on i.c_invoice_id = il.c_invoice_id                 
	  JOIN c_orderline ol ON ol.c_orderline_id = il.c_orderline_id 
	  JOIN fin_payment_schedule fps ON fps.c_invoice_id = i.c_invoice_id
	  JOIN fin_payment_scheduledetail fpsd ON fps.fin_payment_schedule_id = fpsd.fin_payment_schedule_invoice
	  JOIN fin_payment_detail fpd ON fpd.fin_payment_detail_id = fpsd.fin_payment_detail_id
	WHERE EXISTS (SELECT 1
		 FROM c_invoice i2
		   JOIN c_invoiceline il2 on i2.c_invoice_id = il2.c_invoice_id
		   JOIN c_orderline ol2 ON ol2.c_orderline_id = il2.c_orderline_id 
		   JOIN fin_payment_schedule fps2 ON fps2.c_order_id = ol2.c_order_id
		   JOIN fin_payment_scheduledetail fpsd2 ON fps2.fin_payment_schedule_id = fpsd2.fin_payment_schedule_order 
		   JOIN fin_payment_detail fpd2 ON fpd2.fin_payment_detail_id = fpsd2.fin_payment_detail_id
		WHERE fpd2.fin_payment_id = fpd.fin_payment_id
		   AND ol2.c_order_id = ol.c_order_id
		   AND i2.docstatus = 'VO')
	   AND i.docstatus <> 'VO'
	   AND fps.amount <> fpsd.amount
  ) 
  LOOP
    --Fix the current PSD
    RAISE NOTICE '%','Processing PSD of invoice: ' || Cur_PSD.invdocumentno || ' PSD ID: ' || Cur_PSD.fin_payment_scheduledetail_id;
    UPDATE fin_payment_scheduledetail
	SET amount = Cur_PSD.InvoicePSAmount
	WHERE fin_payment_scheduledetail_id = Cur_PSD.fin_payment_scheduledetail_id;
  END LOOP;
END;   $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

--Run the script
SELECT fix_psd_data();

--Remove the created function as it will not used any more
DROP FUNCTION fix_psd_data();