sql/Pg-upgrade2/link_requirement_spec_to_orders_created_from_quotations_created_from_requirement_spec.sql

   1 -- @tag: link_requirement_spec_to_orders_created_from_quotations_created_from_requirement_spec
   2 -- @description: Pflichtenhefte mit Aufträgen verknüpfen, die aus Angeboten erstellt wurden, die wiederum aus einem Pflichtenheft erstellt wurden
   3 -- @depends: release_3_2_0
   4 CREATE TEMPORARY TABLE temp_link_requirement_spec_to_orders AS
   5 SELECT rs_orders.requirement_spec_id, orders.id AS order_id, rs_orders.version_id
   6 FROM record_links rl,
   7   requirement_spec_orders rs_orders,
   8   oe quotations,
   9   oe orders
  10 WHERE (rl.from_table      = 'oe')
  11   AND (rl.from_id         = quotations.id)
  12   AND (rl.to_table        = 'oe')
  13   AND (rl.to_id           = orders.id)
  14   AND (rs_orders.order_id = quotations.id)
  15   AND     COALESCE(quotations.quotation, FALSE)
  16   AND NOT COALESCE(orders.quotation,     FALSE)
  17   AND (quotations.customer_id IS NOT NULL)
  18   AND (orders.customer_id     IS NOT NULL);
  19
  20 INSERT INTO requirement_spec_orders (requirement_spec_id, order_id, version_id)
  21 SELECT requirement_spec_id, order_id, version_id
  22 FROM temp_link_requirement_spec_to_orders new_orders
  23 WHERE NOT EXISTS (
  24   SELECT existing_orders.id
  25   FROM requirement_spec_orders existing_orders
  26   WHERE (existing_orders.requirement_spec_id = new_orders.requirement_spec_id)
  27     AND (existing_orders.order_id            = new_orders.order_id)
  28   LIMIT 1
  29 );