+ $sql = "SELECT
+ trans.type,
+ trans.trans_no,
+ IF(trans.supp_reference='',trans.reference,trans.supp_reference) as reference,
+ trans.tran_date,
+ supplier.supp_name,
+ supplier.curr_code,
+ ov_amount+ov_gst+ov_discount AS Total,
+ trans.alloc,
+ trans.due_date,
+ trans.supplier_id,
+ supplier.address,
+ round(abs(ov_amount+ov_gst+ov_discount)-alloc,6) <= 0 AS settled
+ FROM "
+ .TB_PREF."supp_trans as trans, "
+ .TB_PREF."suppliers as supplier"
+ ." WHERE trans.supplier_id=supplier.supplier_id
+ AND type IN(".ST_SUPPAYMENT.",".ST_SUPPCREDIT.",".ST_BANKPAYMENT.") AND (trans.ov_amount < 0)";
+
+ if (!$settled)
+ $sql .= " AND (round(abs(ov_amount+ov_gst+ov_discount)-alloc,6) > 0)";
+
+ if ($supplier_id != null)
+ $sql .= " AND supplier.supplier_id = ".db_escape($supplier_id);
+
+ return $sql;
+}
+
+function get_allocatable_purch_orders($supplier_id = null, $trans_no=null, $type=null)
+{
+ $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,
+ porder.ord_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."
+ LEFT JOIN ".TB_PREF."grn_batch as grn ON porder.order_no = grn.purch_order_no
+ WHERE total>0";
+
+ if ($trans_no != null and $type != null)