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);
79 foreach ($taxes as $taxitem)
81 $tax_total += $taxitem['Value'];
84 $invoice_items_total = $supp_trans->get_total_charged($supp_trans->tax_group_id);
86 if ($supp_trans->is_invoice)
91 // let's negate everything because it's a credit note
92 $invoice_items_total = -$invoice_items_total;
93 $tax_total = -$tax_total;
94 $supp_trans->ov_discount = -$supp_trans->ov_discount; // this isn't used at all...
97 $date_ = $supp_trans->tran_date;
99 /*First insert the invoice into the supp_trans table*/
100 $invoice_id = add_supp_trans($trans_type, $supp_trans->supplier_id, $date_, $supp_trans->due_date,
101 $supp_trans->reference, $supp_trans->supp_reference,
102 $invoice_items_total, $tax_total, $supp_trans->ov_discount);
104 /* Now the control account */
105 $supplier_accounts = get_supplier_accounts($supp_trans->supplier_id);
106 add_gl_trans_supplier($trans_type, $invoice_id, $date_, $supplier_accounts["payable_account"], 0, 0,
107 -($invoice_items_total + $tax_total + $supp_trans->ov_discount),
108 $supp_trans->supplier_id,
109 "The general ledger transaction for the control total could not be added");
111 /*Loop through the GL Entries and create a debit posting for each of the accounts entered */
113 /*the postings here are a little tricky, the logic goes like this:
114 if its a general ledger amount it goes straight to the account specified
116 if its a GRN amount invoiced then :
118 The cost as originally credited to GRN suspense on arrival of items is debited to GRN suspense. Any difference
119 between the std cost and the currency cost charged as converted at the ex rate of of the invoice is written off
120 to the purchase price variance account applicable to the item being invoiced.
123 foreach ($supp_trans->gl_codes as $entered_gl_code)
126 /*GL Items are straight forward - just do the debit postings to the GL accounts specified -
127 the credit is to creditors control act done later for the total invoice value + tax*/
129 if (!$supp_trans->is_invoice)
130 $entered_gl_code->amount = -$entered_gl_code->amount;
132 $memo_ = $entered_gl_code->memo_;
133 add_gl_trans_supplier($trans_type, $invoice_id, $date_, $entered_gl_code->gl_code,
134 $entered_gl_code->gl_dim, $entered_gl_code->gl_dim2, $entered_gl_code->amount, $supp_trans->supplier_id);
136 add_supp_invoice_gl_item($trans_type, $invoice_id, $entered_gl_code->gl_code,
137 $entered_gl_code->amount, $memo_);
140 foreach ($supp_trans->grn_items as $entered_grn)
143 if (!$supp_trans->is_invoice)
145 $entered_grn->this_quantity_inv = -$entered_grn->this_quantity_inv;
148 $line_taxfree = $entered_grn->taxfree_charge_price($supp_trans->tax_group_id);
149 $line_tax = $entered_grn->full_charge_price($supp_trans->tax_group_id) - $line_taxfree;
151 update_supp_received_items_for_invoice($entered_grn->id, $entered_grn->po_detail_item,
152 $entered_grn->this_quantity_inv, $entered_grn->chg_price);
154 $stock_gl_code = get_stock_gl_code($entered_grn->item_code);
155 $stock_entry_account = $stock_gl_code["inventory_account"];
157 add_gl_trans_supplier($trans_type, $invoice_id, $date_, $stock_entry_account,
158 $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'],
159 $entered_grn->this_quantity_inv * $line_taxfree, $supp_trans->supplier_id);
162 $supplierCurrency = get_supplier_currency($supp_trans->supplier_id);
163 $localChgPrice = to_home_currency($entered_grn->chg_price, $supplierCurrency, $date_);
164 $PurchPriceVar = $entered_grn->this_quantity_inv * ($localChgPrice - $entered_grn->std_cost_unit);
166 echo "purchase price variance is $PurchPriceVar";
168 // Yes but where to post this difference to - if its an inventory item the variance account
169 // must be retreived from the stock category record
171 if ($PurchPriceVar !=0){ // don't bother with this lot if there is no difference !
172 // need to get the stock category record for this inventory item -
173 $stock_gl_code = get_stock_gl_code($entered_grn->item_code);
176 add_gl_trans_supplier($trans_type, $invoice_id, $date_, $stock_gl_code["purch_price_var_act"],
177 $PurchPriceVar, $supp_trans->supplier_id,
178 "The general ledger transaction could not be added for the price variance of the inventory item");
181 add_supp_invoice_item($trans_type, $invoice_id, $entered_grn->item_code,
182 $entered_grn->item_description, 0, $line_taxfree, $line_tax,
183 $entered_grn->this_quantity_inv, $entered_grn->id, $entered_grn->po_detail_item, "");
184 } /* end of GRN postings */
186 /* Now the TAX account */
187 foreach ($taxes as $taxitem)
189 if ($taxitem['Value'] != 0)
192 if (!$supp_trans->is_invoice)
193 $taxitem['Value'] = -$taxitem['Value'];
194 // here we suppose that tax is never included in price (we are company customer).
195 add_supp_invoice_tax_item($trans_type, $invoice_id, $taxitem['tax_type_id'],
196 $taxitem['rate'], 0, $taxitem['Value']);
198 add_gl_trans_supplier($trans_type, $invoice_id, $date_,
199 $taxitem['purchasing_gl_code'], 0, 0, $taxitem['Value'],
200 $supp_trans->supplier_id,
201 "A general ledger transaction for the tax amount could not be added");
205 add_comments($trans_type, $invoice_id, $date_, $supp_trans->Comments);
207 references::save_last($supp_trans->reference, $trans_type);
209 commit_transaction();
214 //----------------------------------------------------------------------------------------
216 // get all the invoices/credits for a given PO - quite long route to get there !
218 function get_po_invoices_credits($po_number)
220 $sql = "SELECT DISTINCT ".TB_PREF."supp_trans.trans_no, ".TB_PREF."supp_trans.type,
221 ov_amount+ov_discount+ov_gst AS Total,
222 ".TB_PREF."supp_trans.tran_date
223 FROM ".TB_PREF."supp_trans, ".TB_PREF."supp_invoice_items, ".TB_PREF."purch_order_details
224 WHERE ".TB_PREF."supp_invoice_items.supp_trans_no = ".TB_PREF."supp_trans.trans_no
225 AND ".TB_PREF."supp_invoice_items.po_detail_item_id = ".TB_PREF."purch_order_details.po_detail_item
226 AND ".TB_PREF."purch_order_details.order_no = $po_number";
228 return db_query($sql, "The invoices/credits for the po $po_number could not be retreived");
231 //----------------------------------------------------------------------------------------
233 function read_supp_invoice($trans_no, $trans_type, &$supp_trans)
235 $sql = "SELECT ".TB_PREF."supp_trans.*, supp_name FROM ".TB_PREF."supp_trans,".TB_PREF."suppliers
236 WHERE trans_no = $trans_no AND type = $trans_type
237 AND ".TB_PREF."suppliers.supplier_id=".TB_PREF."supp_trans.supplier_id";
238 $result = db_query($sql, "Cannot retreive a supplier transaction");
240 if (db_num_rows($result) == 1)
242 $trans_row = db_fetch($result);
244 $supp_trans->supplier_id = $trans_row["supplier_id"];
245 $supp_trans->supplier_name = $trans_row["supp_name"];
246 $supp_trans->tran_date = sql2date($trans_row["tran_date"]);
247 $supp_trans->due_date = sql2date($trans_row["due_date"]);
248 //$supp_trans->Comments = $trans_row["TransText"];
249 $supp_trans->Comments = "";
250 $supp_trans->reference = $trans_row["reference"];
251 $supp_trans->supp_reference = $trans_row["supp_reference"];
252 $supp_trans->ov_amount = $trans_row["ov_amount"];
253 $supp_trans->ov_discount = $trans_row["ov_discount"];
254 $supp_trans->ov_gst = $trans_row["ov_gst"];
256 $id = $trans_row["trans_no"];
258 $result = get_supp_invoice_items($trans_type, $id);
260 if (db_num_rows($result) > 0)
263 while ($details_row = db_fetch($result))
266 if ($details_row["gl_code"] == 0)
268 $supp_trans->add_grn_to_trans($details_row["grn_item_id"], $details_row["po_detail_item_id"], $details_row["stock_id"],
269 $details_row["description"], 0, 0, $details_row["quantity"], 0, $details_row["FullUnitPrice"],
274 $supp_trans->add_gl_codes_to_trans($details_row["gl_code"], get_gl_account_name($details_row["gl_code"]), 0, 0,
275 $details_row["FullUnitPrice"], $details_row["memo_"]);
281 return display_db_error("Invalid supptrans details for supptrans number : $trans_no and type : $trans_type", $sql, true);
287 return display_db_error("Invalid supptrans number : $trans_no and type : $trans_type", $sql, true);
291 //----------------------------------------------------------------------------------------
293 function void_supp_invoice($type, $type_no)
297 void_bank_trans($type, $type_no, true);
299 void_gl_trans($type, $type_no, true);
301 void_supp_allocations($type, $type_no);
303 void_supp_trans($type, $type_no);
305 $result = get_supp_invoice_items($type, $type_no);
307 // now remove this invoice/credit from any GRNs/POs that it's related to
308 if (db_num_rows($result) > 0)
310 while ($details_row = db_fetch($result))
312 if (strlen($details_row["grn_item_id"]) > 0) // it can be empty for GL items
314 update_supp_received_items_for_invoice($details_row["grn_item_id"],
315 $details_row["po_detail_item_id"], -$details_row["quantity"]);
320 void_supp_invoice_items($type, $type_no);
321 void_supp_invoice_tax_items($type, $type_no);
323 commit_transaction();
326 //----------------------------------------------------------------------------------------