CREATE OR REPLACE FUNCTION fix_issue35683_data()
  RETURNS VOID AS
$BODY$ 
DECLARE
 Cur_inv RECORD;
 v_payment_schedule_invoice_id VARCHAR(32);
 v_payment_schedule_order_id VARCHAR(32);
 v_fin_payment_detail_id VARCHAR(32);
 v_dummy_payment_detail_id VARCHAR(32);
 v_client_id VARCHAR(32);
 v_org_id VARCHAR(32);
 v_fin_payment_id VARCHAR(32);
 v_payment_detail_total FIN_Payment_Schedule.Amount%TYPE;
   
BEGIN
 FOR Cur_inv IN
    (SELECT distinct (o.c_order_id), o.grandtotal * -1 as order_total, il.c_invoice_id, (SELECT grandtotal FROM c_invoice WHERE c_invoice_id = il.c_invoice_id) as invoice_total FROM c_order o
        JOIN c_orderline ol ON ol.c_order_id = o.c_order_id
        JOIN c_invoiceline il ON il.c_orderline_id = ol.c_orderline_id
	JOIN c_doctype odtarget on o.c_doctypetarget_id = odtarget.c_doctype_id
      WHERE o.issotrx = 'N'
        AND o.grandtotal <> 0
        AND odtarget.isreturn = 'Y'
        AND (SELECT ispaid  FROM c_invoice WHERE c_invoice_id = il.c_invoice_id) = 'Y'  
        AND EXISTS (SELECT 1 FROM c_invoice i
          JOIN c_doctype idtarget on i.c_doctypetarget_id = idtarget.c_doctype_id
          WHERE i.c_invoice_id = il.c_invoice_id
            AND idtarget.docbasetype = 'APC') 
        AND EXISTS (SELECT 1 FROM fin_payment_scheduledetail ifpsd
                     JOIN fin_payment_schedule ifps ON ifpsd.fin_payment_schedule_invoice = ifps.fin_payment_schedule_id
                    WHERE ifps.c_invoice_id = il.c_invoice_id
                     AND NOT EXISTS 
                        (SELECT 1 FROM fin_payment_scheduledetail WHERE ifpsd.fin_payment_schedule_order = fin_payment_schedule_order AND fin_payment_detail_id IS NOT NULL))) 
  LOOP
    --Fix the current return to vendor payment out plan
    RAISE NOTICE '%','Processing return to vendor: ' || Cur_inv.c_order_id || ' with invoice: ' || Cur_inv.c_invoice_id;

    --Get invoice payment schedule
    SELECT fin_payment_schedule_id INTO v_payment_schedule_invoice_id 
     FROM fin_payment_schedule 
    WHERE c_invoice_id = Cur_inv.c_invoice_id;

    --Get order payment schedule
    SELECT fin_payment_schedule_id INTO v_payment_schedule_order_id 
     FROM fin_payment_schedule 
    WHERE c_order_id = Cur_inv.c_order_id;

    --Get payment detail id
    SELECT fin_payment_detail_id INTO v_fin_payment_detail_id 
     FROM fin_payment_scheduledetail 
     WHERE fin_payment_schedule_invoice = v_payment_schedule_invoice_id
       AND fin_payment_detail_id IS NOT NULL;   

   --Update the return to vendor payment schedule amount
   UPDATE fin_payment_schedule
    SET paidamt = amount,
     outstandingamt = 0,
     updated = now(),
     updatedby = '100'
   WHERE c_order_id = Cur_inv.c_order_id;    

    --Orders with different invoice amount
    IF (Cur_inv.order_total <> Cur_inv.invoice_total AND EXISTS (SELECT 1 FROM c_orderline ol 
         JOIN c_invoiceline il ON il.c_orderline_id = ol.c_orderline_id
        WHERE ol.c_order_id = Cur_inv.c_order_id
          AND il.c_invoice_id = Cur_inv.c_invoice_id
          AND ol.priceactual <> il.priceactual)) THEN 
    --Order with a wrong payment asociated to the invoice
      IF(EXISTS (SELECT 1 FROM fin_payment_scheduledetail WHERE fin_payment_schedule_invoice = v_payment_schedule_invoice_id
           AND fin_payment_schedule_order = v_payment_schedule_order_id AND fin_payment_detail_id = v_fin_payment_detail_id)) THEN
        UPDATE fin_payment_scheduledetail 
         SET  fin_payment_schedule_order = NULL
         WHERE fin_payment_schedule_order = v_payment_schedule_order_id
          AND fin_payment_detail_id = v_fin_payment_detail_id
          AND fin_payment_schedule_invoice = v_payment_schedule_invoice_id;  
      END IF;
	--Update the invoice payment schedule detail with the payment detail id and order payment schedule amount
        UPDATE fin_payment_scheduledetail
          SET fin_payment_detail_id = v_fin_payment_detail_id,
           amount = (SELECT amount FROM fin_payment_schedule WHERE fin_payment_schedule_id = v_payment_schedule_order_id),
           updated = now(),
           updatedby = '100'
        WHERE fin_payment_schedule_invoice = v_payment_schedule_invoice_id
         AND fin_payment_schedule_order = v_payment_schedule_order_id;

        v_dummy_payment_detail_id := get_uuid();
        v_payment_detail_total := (SELECT amount FROM fin_payment_schedule WHERE fin_payment_schedule_id = v_payment_schedule_order_id) 
              - (SELECT amount FROM fin_payment_schedule WHERE fin_payment_schedule_id = v_payment_schedule_invoice_id);
      
        v_payment_detail_total := v_payment_detail_total * -1;

        --Read the payment detail
        SELECT ad_client_id, ad_org_id, fin_payment_id
         INTO v_client_id, v_org_id, v_fin_payment_id
         FROM fin_payment_detail 
        WHERE fin_payment_detail_id = v_fin_payment_detail_id;

        UPDATE fin_payment
         SET processed = 'N'
        WHERE fin_payment_id = v_fin_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, v_client_id, v_org_id, 
                 now(), '100', now(), '100', 
                 v_fin_payment_id, v_payment_detail_total, 'N', 'Y',
                 0, NULL, 'N');

        UPDATE fin_payment
         SET processed = 'Y'
        WHERE fin_payment_id = v_fin_payment_id;
                 
        UPDATE fin_payment_scheduledetail
          SET fin_payment_detail_id = v_dummy_payment_detail_id,
           amount = v_payment_detail_total,
           updated = now(),
           updatedby = '100'
        WHERE fin_payment_schedule_invoice = v_payment_schedule_invoice_id
         AND fin_payment_schedule_order IS NULL
         AND fin_payment_detail_id IS NOT NULL;

        --Remove invoice payment schedule detail with empty payment schedule order and payment detail
        DELETE FROM fin_payment_scheduledetail fpsd  
         WHERE fpsd.fin_payment_schedule_order IS NULL
          AND fpsd.fin_payment_detail_id IS NULL
          AND fpsd.fin_payment_schedule_invoice = v_payment_schedule_invoice_id;            
    ELSE
     IF(EXISTS (SELECT 1 FROM fin_payment_scheduledetail WHERE fin_payment_schedule_invoice = v_payment_schedule_invoice_id
           AND fin_payment_schedule_order = v_payment_schedule_order_id)) THEN
       --Update the invoice payment schedule detail with the payment detail id
       UPDATE fin_payment_scheduledetail
        SET fin_payment_detail_id = v_fin_payment_detail_id,
         amount = amount * -1,
         updated = now(),
         updatedby = '100'
       WHERE fin_payment_schedule_invoice = v_payment_schedule_invoice_id
         AND fin_payment_schedule_order = v_payment_schedule_order_id;
     ELSE
       UPDATE fin_payment_scheduledetail
        SET fin_payment_schedule_order = v_payment_schedule_order_id,
         updated = now(),
         updatedby = '100'
       WHERE fin_payment_schedule_invoice = v_payment_schedule_invoice_id
         AND fin_payment_detail_id = v_fin_payment_detail_id;
     END IF;
     
      --Remove invoice payment schedule detail with empty payment schedule order
      DELETE FROM fin_payment_scheduledetail fpsd  
       WHERE fpsd.fin_payment_schedule_order IS NULL
        AND fpsd.fin_payment_schedule_invoice = v_payment_schedule_invoice_id;
    END IF;   

    --Delete all order payment schedule detail with empty payment detail
    DELETE FROM fin_payment_scheduledetail fpsd  
    WHERE fpsd.fin_payment_schedule_order = v_payment_schedule_order_id
      AND fin_payment_detail_id IS NULL;
           
  END LOOP;
END;   $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;


--Run the script
SELECT fix_issue35683_data();

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