+ $sql = "SELECT
+ ".ST_PURCHORDER." as type,
+ porder.order_no as trans_no,
+ porder.reference,
+ porder.ord_date,
+ supplier.supp_name AS DebtorName,
+ supplier.curr_code,
+ total as Total,
+ porder.alloc,
+ due_dates.date as due_date,
+ supplier.address,
+ amt,
+ requisition_no as supp_ref
+ FROM ".TB_PREF."purch_orders as porder
+ LEFT JOIN ".TB_PREF."suppliers as supplier ON porder.supplier_id = supplier.supplier_id
+ LEFT JOIN ".TB_PREF."supp_allocations as alloc
+ ON porder.order_no = alloc.trans_no_to AND alloc.trans_type_to=".ST_PURCHORDER." AND alloc.person_id=porder.supplier_id
+ LEFT JOIN ".TB_PREF."grn_batch as grn ON porder.order_no = grn.purch_order_no
+ LEFT JOIN ($due_dates) due_dates ON due_dates.order_no=porder.order_no
+ WHERE total>0";
+
+ if ($trans_no != null and $type != null)
+ {
+ $sql .= " AND alloc.trans_no_from=".db_escape($trans_no)."
+ AND alloc.trans_type_from=".db_escape($type);
+ }
+ else
+ {
+ $sql .= " AND ISNULL(grn.purch_order_no)"; // only sales orders no yet received
+ }
+ if ($supplier_id)
+ $sql .= " AND porder.supplier_id=".db_escape($supplier_id);
+
+ $sql .= " GROUP BY porder.order_no, grn.purch_order_no";
+
+ return $sql;
+}