Recalculates order perspective

recalculate order perspective.sql

  DO $$
    DECLARE order_calc record;
    begin
        for order_calc in (
          select o.id order_id, oi.vendor_id, oi.vendor_id,
          sum(oi.price * oi.quantity) item_total,
          sum(oi.coupon_total_discount) coupon_total_discount, 
          sum(oi.shipping_estimated_cost) shipping_estimated_cost, 
          sum(oi.price * oi.quantity) + sum(oi.shipping_estimated_cost) - sum(oi.coupon_total_discount) net_total
            from order_header o
            inner join order_item_detail oi on oi.order_header_id  = o.id
            where o.id in (
              -- defective orders
               select order_header_id from order_header_vendor_perspective ohvp where
                (net_total is null or item_total is null or shipping_total  is null) 
            )
            group by o.id, oi.vendor_id
        )
        loop
      update order_header_vendor_perspective
        set
          item_total  = order_calc.item_total,
          shipping_total= order_calc.shipping_estimated_cost,
          total_coupon_discount = order_calc.coupon_total_discount,
          net_total  = order_calc.net_total
        where order_header_id = order_calc.order_id
          and vendor_id = order_calc.vendor_id
       ;
      RAISE NOTICE 'processed order id  % for vendor id % ',order_calc.order_id, order_calc.vendor_id;
        end loop;
    END
$$;