3 include_once($path_to_root . "/purchasing/includes/db/invoice_items_db.inc");
5 //--------------------------------------------------------------------------------------------------
7 function read_supplier_details_to_trans(&$supp_trans, $supplier_id)
9 $sql = "SELECT ".TB_PREF."suppliers.supp_name, ".TB_PREF."payment_terms.terms, ".TB_PREF."payment_terms.days_before_due,
10 ".TB_PREF."payment_terms.day_in_following_month,
11 ".TB_PREF."suppliers.tax_group_id, ".TB_PREF."tax_groups.name As tax_group_name
12 From ".TB_PREF."suppliers, ".TB_PREF."payment_terms, ".TB_PREF."tax_groups
13 WHERE ".TB_PREF."suppliers.tax_group_id = ".TB_PREF."tax_groups.id
14 AND ".TB_PREF."suppliers.payment_terms=".TB_PREF."payment_terms.terms_indicator
15 AND ".TB_PREF."suppliers.supplier_id = '" . $supplier_id . "'";
17 $result = db_query($sql, "The supplier record selected: " . $supplier_id . " cannot be retrieved");
19 $myrow = db_fetch($result);
21 $supp_trans->supplier_id = $supplier_id;
22 $supp_trans->supplier_name = $myrow['supp_name'];
23 $supp_trans->terms_description = $myrow['terms'];
25 if ($myrow['days_before_due'] == 0)
27 $supp_trans->terms = "1" . $myrow['day_in_following_month'];
31 $supp_trans->terms = "0" . $myrow['days_before_due'];
33 $supp_trans->tax_description = $myrow['tax_group_name'];
34 $supp_trans->tax_group_id = $myrow['tax_group_id'];
36 if ($supp_trans->tran_date == "")
38 $supp_trans->tran_date = Today();
39 if (!is_date_in_fiscalyear($supp_trans->tran_date))
40 $supp_trans->tran_date = end_fiscalyear();
42 //if ($supp_trans->due_date=="") {
43 // get_duedate_from_terms($supp_trans);
45 get_duedate_from_terms($supp_trans);
48 //--------------------------------------------------------------------------------------------------
50 function update_supp_received_items_for_invoice($id, $po_detail_item, $qty_invoiced, $chg_price=null)
52 $sql = "UPDATE ".TB_PREF."purch_order_details
53 SET qty_invoiced = qty_invoiced + $qty_invoiced ";
55 if ($chg_price != null)
56 $sql .= " , act_price = $chg_price ";
58 $sql .= " WHERE po_detail_item = $po_detail_item";
59 db_query($sql, "The quantity invoiced of the purchase order line could not be updated");
61 $sql = "UPDATE ".TB_PREF."grn_items
62 SET quantity_inv = quantity_inv + $qty_invoiced
64 db_query($sql, "The quantity invoiced off the items received record could not be updated");
67 //----------------------------------------------------------------------------------------
69 function add_supp_invoice($supp_trans) // do not receive as ref because we change locally
71 $company_currency = get_company_currency();
73 /*Start an sql transaction */
77 $taxes = $supp_trans->get_taxes($supp_trans->tax_group_id);
78 foreach ($taxes as $taxitem)
80 $tax_total += $taxitem['Value'];
83 $invoice_items_total = $supp_trans->get_total_charged($supp_trans->tax_group_id);
85 if ($supp_trans->is_invoice)
90 // let's negate everything because it's a credit note
91 $invoice_items_total = -$invoice_items_total;
92 $tax_total = -$tax_total;
93 $supp_trans->ov_discount = -$supp_trans->ov_discount; // this isn't used at all...
96 $date_ = $supp_trans->tran_date;
98 /*First insert the invoice into the supp_trans table*/
99 $invoice_id = add_supp_trans($trans_type, $supp_trans->supplier_id, $date_, $supp_trans->due_date,
100 $supp_trans->reference, $supp_trans->supp_reference,
101 $invoice_items_total, $tax_total, $supp_trans->ov_discount);
103 /* Now the control account */
104 $supplier_accounts = get_supplier_accounts($supp_trans->supplier_id);
105 add_gl_trans_supplier($trans_type, $invoice_id, $date_, $supplier_accounts["payable_account"], 0, 0,
106 -($invoice_items_total + $tax_total + $supp_trans->ov_discount),
107 $supp_trans->supplier_id,
108 "The general ledger transaction for the control total could not be added");
110 /*Loop through the GL Entries and create a debit posting for each of the accounts entered */
112 /*the postings here are a little tricky, the logic goes like this:
113 if its a general ledger amount it goes straight to the account specified
115 if its a GRN amount invoiced then :
117 The cost as originally credited to GRN suspense on arrival of items is debited to GRN suspense. Any difference
118 between the std cost and the currency cost charged as converted at the ex rate of of the invoice is written off
119 to the purchase price variance account applicable to the item being invoiced.
122 foreach ($supp_trans->gl_codes as $entered_gl_code)
125 /*GL Items are straight forward - just do the debit postings to the GL accounts specified -
126 the credit is to creditors control act done later for the total invoice value + tax*/
128 if (!$supp_trans->is_invoice)
129 $entered_gl_code->amount = -$entered_gl_code->amount;
131 $memo_ = $entered_gl_code->memo_;
132 add_gl_trans_supplier($trans_type, $invoice_id, $date_, $entered_gl_code->gl_code,
133 $entered_gl_code->gl_dim, $entered_gl_code->gl_dim2, $entered_gl_code->amount, $supp_trans->supplier_id);
135 add_supp_invoice_gl_item($trans_type, $invoice_id, $entered_gl_code->gl_code,
136 $entered_gl_code->amount, $memo_);
139 foreach ($supp_trans->grn_items as $entered_grn)
142 if (!$supp_trans->is_invoice)
144 $entered_grn->this_quantity_inv = -$entered_grn->this_quantity_inv;
147 $line_taxfree = $entered_grn->taxfree_charge_price($supp_trans->tax_group_id);
148 $line_tax = $entered_grn->full_charge_price() - $line_taxfree;
150 update_supp_received_items_for_invoice($entered_grn->id, $entered_grn->po_detail_item,
151 $entered_grn->this_quantity_inv, $entered_grn->chg_price);
153 $stock_gl_code = get_stock_gl_code($entered_grn->item_code);
154 $stock_entry_account = $stock_gl_code["inventory_account"];
156 add_gl_trans_supplier($trans_type, $invoice_id, $date_, $stock_entry_account,
157 $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'],
158 $entered_grn->this_quantity_inv * $line_taxfree, $supp_trans->supplier_id);
161 $supplierCurrency = get_supplier_currency($supp_trans->supplier_id);
162 $localChgPrice = to_home_currency($entered_grn->chg_price, $supplierCurrency, $date_);
163 $PurchPriceVar = $entered_grn->this_quantity_inv * ($localChgPrice - $entered_grn->std_cost_unit);
165 echo "purchase price variance is $PurchPriceVar";
167 // Yes but where to post this difference to - if its an inventory item the variance account
168 // must be retreived from the stock category record
170 if ($PurchPriceVar !=0){ // don't bother with this lot if there is no difference !
171 // need to get the stock category record for this inventory item -
172 $stock_gl_code = get_stock_gl_code($entered_grn->item_code);
175 add_gl_trans_supplier($trans_type, $invoice_id, $date_, $stock_gl_code["purch_price_var_act"],
176 $PurchPriceVar, $supp_trans->supplier_id,
177 "The general ledger transaction could not be added for the price variance of the inventory item");
180 add_supp_invoice_item($trans_type, $invoice_id, $entered_grn->item_code,
181 $entered_grn->item_description, 0, $line_taxfree, $line_tax,
182 $entered_grn->this_quantity_inv, $entered_grn->id, $entered_grn->po_detail_item, "");
183 } /* end of GRN postings */
185 /* Now the TAX account */
186 foreach ($taxes as $taxitem)
188 if ($taxitem['Value'] != 0)
191 if (!$supp_trans->is_invoice)
192 $taxitem['Value'] = -$taxitem['Value'];
194 add_supp_invoice_tax_item($trans_type, $invoice_id, $taxitem['tax_type_id'],
195 $taxitem['rate'], $taxitem['included_in_price'], $taxitem['Value']);
197 add_gl_trans_supplier($trans_type, $invoice_id, $date_,
198 $taxitem['purchasing_gl_code'], 0, 0, $taxitem['Value'],
199 $supp_trans->supplier_id,
200 "A general ledger transaction for the tax amount could not be added");
204 add_comments($trans_type, $invoice_id, $date_, $supp_trans->Comments);
206 references::save_last($supp_trans->reference, $trans_type);
208 commit_transaction();
213 //----------------------------------------------------------------------------------------
215 // get all the invoices/credits for a given PO - quite long route to get there !
217 function get_po_invoices_credits($po_number)
219 $sql = "SELECT DISTINCT ".TB_PREF."supp_trans.trans_no, ".TB_PREF."supp_trans.type,
220 ov_amount+ov_discount+ov_gst AS Total,
221 ".TB_PREF."supp_trans.tran_date
222 FROM ".TB_PREF."supp_trans, ".TB_PREF."supp_invoice_items, ".TB_PREF."purch_order_details
223 WHERE ".TB_PREF."supp_invoice_items.supp_trans_no = ".TB_PREF."supp_trans.trans_no
224 AND ".TB_PREF."supp_invoice_items.po_detail_item_id = ".TB_PREF."purch_order_details.po_detail_item
225 AND ".TB_PREF."purch_order_details.order_no = $po_number";
227 return db_query($sql, "The invoices/credits for the po $po_number could not be retreived");
230 //----------------------------------------------------------------------------------------
232 function read_supp_invoice($trans_no, $trans_type, &$supp_trans)
234 $sql = "SELECT ".TB_PREF."supp_trans.*, supp_name FROM ".TB_PREF."supp_trans,".TB_PREF."suppliers
235 WHERE trans_no = $trans_no AND type = $trans_type
236 AND ".TB_PREF."suppliers.supplier_id=".TB_PREF."supp_trans.supplier_id";
237 $result = db_query($sql, "Cannot retreive a supplier transaction");
239 if (db_num_rows($result) == 1)
241 $trans_row = db_fetch($result);
243 $supp_trans->supplier_id = $trans_row["supplier_id"];
244 $supp_trans->supplier_name = $trans_row["supp_name"];
245 $supp_trans->tran_date = sql2date($trans_row["tran_date"]);
246 $supp_trans->due_date = sql2date($trans_row["due_date"]);
247 //$supp_trans->Comments = $trans_row["TransText"];
248 $supp_trans->Comments = "";
249 $supp_trans->reference = $trans_row["reference"];
250 $supp_trans->supp_reference = $trans_row["supp_reference"];
251 $supp_trans->ov_amount = $trans_row["ov_amount"];
252 $supp_trans->ov_discount = $trans_row["ov_discount"];
253 $supp_trans->ov_gst = $trans_row["ov_gst"];
255 $id = $trans_row["trans_no"];
257 $result = get_supp_invoice_items($trans_type, $id);
259 if (db_num_rows($result) > 0)
262 while ($details_row = db_fetch($result))
265 if ($details_row["gl_code"] == 0)
267 $supp_trans->add_grn_to_trans($details_row["grn_item_id"], $details_row["po_detail_item_id"], $details_row["stock_id"],
268 $details_row["description"], 0, 0, $details_row["quantity"], 0, $details_row["FullUnitPrice"],
273 $supp_trans->add_gl_codes_to_trans($details_row["gl_code"], get_gl_account_name($details_row["gl_code"]), 0, 0,
274 $details_row["FullUnitPrice"], $details_row["memo_"]);
280 return display_db_error("Invalid supptrans details for supptrans number : $trans_no and type : $trans_type", $sql, true);
286 return display_db_error("Invalid supptrans number : $trans_no and type : $trans_type", $sql, true);
290 //----------------------------------------------------------------------------------------
292 function void_supp_invoice($type, $type_no)
296 void_bank_trans($type, $type_no, true);
298 void_gl_trans($type, $type_no, true);
300 void_supp_allocations($type, $type_no);
302 void_supp_trans($type, $type_no);
304 $result = get_supp_invoice_items($type, $type_no);
306 // now remove this invoice/credit from any GRNs/POs that it's related to
307 if (db_num_rows($result) > 0)
309 while ($details_row = db_fetch($result))
311 if (strlen($details_row["grn_item_id"]) > 0) // it can be empty for GL items
313 update_supp_received_items_for_invoice($details_row["grn_item_id"],
314 $details_row["po_detail_item_id"], -$details_row["quantity"]);
319 void_supp_invoice_items($type, $type_no);
320 void_supp_invoice_tax_items($type, $type_no);
322 commit_transaction();
325 //----------------------------------------------------------------------------------------