+ " ], # make emacs happy again: '
+ ]
+}
+
+sub delivery_plan_query_linked_items {
+ my ($self) = @_;
+ my $vc = $self->vc;
+ my $employee_id = SL::DB::Manager::Employee->current->id;
+ my $oe_owner = $_[0]->all_edit_right ? '' : " oe.employee_id = $employee_id AND";
+
+ [
+ "order.${vc}_id" => { gt => 0 },
+ 'order.closed' => 0,
+ or => [ 'order.quotation' => 0, 'order.quotation' => undef ],
+
+ # filter by shipped_qty < qty, read from innermost to outermost
+ 'id' => [ \"
+ SELECT id FROM (
+ SELECT oi.qty, oi.id, SUM(doi.qty) AS doi_qty
+ FROM orderitems oi, oe, record_links rl, delivery_order_items doi
+ WHERE
+ oe.id = oi.trans_id AND
+ oe.${vc}_id IS NOT NULL AND
+ (oe.quotation = 'f' OR oe.quotation IS NULL) AND
+ NOT oe.closed AND
+ $oe_owner
+ doi.id = rl.to_id AND
+ rl.from_table = 'orderitems'AND
+ rl.to_table = 'delivery_order_items' AND
+ rl.from_id = oi.id
+ GROUP BY oi.id
+ ) linked
+ WHERE qty > doi_qty
+
+ UNION ALL
+
+ -- 2. since the join over record_links fails for items not in any delivery order
+ -- retrieve those without record_links at all
+ SELECT oi.id FROM orderitems oi, oe
+ WHERE
+ oe.id = oi.trans_id AND
+ oe.${vc}_id IS NOT NULL AND
+ (oe.quotation = 'f' OR oe.quotation IS NULL) AND
+ NOT oe.closed AND
+ $oe_owner
+ oi.id NOT IN (
+ SELECT from_id
+ FROM record_links rl
+ WHERE
+ rl.from_table ='orderitems' AND
+ rl.to_table = 'delivery_order_items'
+ )
+
+ " ], # make emacs happy again: " ]