}
// end if negative would result
}
//loop around the autoissue requirements for the work order
}
if ($InputError == false) {
/************************ BEGIN SQL TRANSACTIONS ************************/
$Result = DB_Txn_Begin($db);
/*Now Get the next WOReceipt transaction type 26 - function in SQL_CommonFunctions*/
$WOReceiptNo = GetNextTransNo(26, $db);
$PeriodNo = GetPeriod(Date($_SESSION['DefaultDateFormat']), $db);
if (!isset($_POST['ReceivedDate'])) {
$_POST['ReceivedDate'] = Date($_SESSION['DefaultDateFormat']);
}
$SQLReceivedDate = FormatDateForSQL($_POST['ReceivedDate']);
$StockGLCode = GetStockGLCode($_POST['StockID'], $db);
//Recalculate the standard for the item if there were no items previously received against the work order
if ($WORow['qtyrecd'] == 0) {
$CostResult = DB_query("SELECT SUM((materialcost+labourcost+overheadcost)*bom.quantity) AS cost\n\t\t\t\t\t\t\t\t\tFROM stockmaster INNER JOIN bom\n\t\t\t\t\t\t\t\t\tON stockmaster.stockid=bom.component\n\t\t\t\t\t\t\t\t\tWHERE bom.parent='" . $_POST['StockID'] . "'\n\t\t\t\t\t\t\t\t\tAND bom.loccode='" . $WORow['loccode'] . "'", $db);
$CostRow = DB_fetch_array($CostResult);
if (is_null($CostRow['cost']) or $CostRow['cost'] == 0) {
$Cost = 0;
} else {
$Cost = $CostRow['cost'];
}
//Need to refresh the worequirments with the bom components now incase they changed
$DelWORequirements = DB_query("DELETE FROM worequirements\n\t\t\t\t\t\t\t\t\t\t\tWHERE wo='" . $_POST['WO'] . "'\n\t\t\t\t\t\t\t\t\t\t\tAND parentstockid='" . $_POST['StockID'] . "'", $db);
//Recursively insert real component requirements
WoRealRequirements($db, $_POST['WO'], $WORow['loccode'], $_POST['StockID']);
//Need to check this against the current standard cost and do a cost update if necessary
$sql = "SELECT materialcost+labourcost+overheadcost AS cost,\n\t\t\t\t\t\t sum(quantity) AS totalqoh,\n\t\t\t\t\t\t labourcost,\n\t\t\t\t\t\t overheadcost\n\t\t\t\t\tFROM stockmaster INNER JOIN locstock\n\t\t\t\t\t\tON stockmaster.stockid=locstock.stockid\n\t\t\t\t\tWHERE stockmaster.stockid='" . $_POST['StockID'] . "'\n\t\t\t\t\tGROUP BY\n\t\t\t\t\t\tmaterialcost,\n\t\t\t\t\t\tlabourcost,\n\t\t\t\t\t\toverheadcost";
enter the GL entry to reverse the GRN suspense entry created on delivery at standard cost used on delivery */
if ($EnteredGRN->StdCostUnit * $EnteredGRN->This_QuantityInv != 0) {
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\t\t\tVALUES ('21',\n\t\t\t\t\t\t\t\t\t'" . $CreditNoteNo . "',\n\t\t\t\t\t\t\t\t\t'" . $SQLCreditNoteDate . "',\n\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t'" . $_SESSION['SuppTrans']->GRNAct . "',\n\t\t\t\t\t\t\t\t\t'" . $_SESSION['SuppTrans']->SupplierID . ' - ' . _('GRN Credit Note') . ' ' . $EnteredGRN->GRNNo . ' - ' . $EnteredGRN->ItemCode . ' x ' . $EnteredGRN->This_QuantityInv . ' @ ' . _('std cost of') . ' ' . $EnteredGRN->StdCostUnit . "',\n\t\t\t\t\t\t\t\t \t'" . -$EnteredGRN->StdCostUnit * $EnteredGRN->This_QuantityInv . "')";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The general ledger transaction could not be added because');
$DbgMsg = _('The following SQL to insert the GL transaction was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, True);
}
$PurchPriceVar = $EnteredGRN->This_QuantityInv * ($EnteredGRN->ChgPrice / $_SESSION['SuppTrans']->ExRate - $EnteredGRN->StdCostUnit);
/*Yes but where to post this difference to - if its a stock item the variance account must be retrieved from the stock category record
if its a nominal purchase order item with no stock item then post it to the account specified in the purchase order detail record */
if ($PurchPriceVar != 0) {
/* don't bother with this lot if there is no difference ! */
if (mb_strlen($EnteredGRN->ItemCode) > 0 or $EnteredGRN->ItemCode != '') {
/*so it is a stock item */
/*need to get the stock category record for this stock item - this is function in SQL_CommonFunctions.inc */
$StockGLCode = GetStockGLCode($EnteredGRN->ItemCode, $db);
/*We have stock item and a purchase price variance need to see whether we are using Standard or WeightedAverageCosting */
if ($_SESSION['WeightedAverageCosting'] == 1) {
/*Weighted Average costing */
/*
First off figure out the new weighted average cost Need the following data:
How many in stock now
The quantity being invoiced here - $EnteredGRN->This_QuantityInv
The cost of these items - $EnteredGRN->ChgPrice / $_SESSION['SuppTrans']->ExRate
*/
$sql = "SELECT SUM(quantity) FROM locstock WHERE stockid='" . $EnteredGRN->ItemCode . "'";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The quantity on hand could not be retrieved from the database');
$DbgMsg = _('The following SQL to retrieve the total stock quantity was used');
$Result = DB_query($sql, $db, $ErrMsg, $DbgMsg, True);
$QtyRow = DB_fetch_row($Result);
function CreateCreditNote($Header, $LineDetails, $User, $Password)
{
/* Create a customer credit note in KwaMoja.
* Needs an associative array for the $Header
* and an array of assocative arrays for the $LineDetails
* $Header contains an associative array in the format:
* Header['debtorno'] - the customer code
* Header['branchcode'] - the branch code
* Header['trandate'] - the date of the credit note
* Header['tpe'] - the sales type
* Header['fromstkloc'] - the inventory location where the stock is put back into
* Header['customerref'] - the customer's reference
* Header['shipvia'] - the shipper required by KwaMoja
*
* and $LineDetails contains an array of associative arrays of the format:
*
* $LineDetails[0]['stockid']
* $LineDetails[0]['price']
* $LineDetails[0]['qty'] - expected to be a negative quantity (a negative sale)
* $LineDetails[0]['discountpercent']
*/
$Errors = array();
$db = db($User, $Password);
if (gettype($db) == 'integer') {
$Errors[0] = NoAuthorisation;
return $Errors;
}
$Errors = VerifyDebtorExists($Header['debtorno'], sizeof($Errors), $Errors, $db);
$Errors = VerifyBranchNoExists($Header['debtorno'], $Header['branchcode'], sizeof($Errors), $Errors, $db);
/*Does not deal with serialised/lot track items - for use by POS */
/*Get Company Defaults */
$ReadCoyResult = api_DB_query("SELECT debtorsact,\n\t\t\t\t\t\t\t\t\t\t\t\tfreightact,\n\t\t\t\t\t\t\t\t\t\t\t\tgllink_debtors,\n\t\t\t\t\t\t\t\t\t\t\t\tgllink_stock\n\t\t\t\t\t\t\t\t\t\tFROM companies\n\t\t\t\t\t\t\t\t\t\tWHERE coycode=1", $db);
$CompanyRecord = DB_fetch_array($ReadCoyResult);
if (DB_error_no($db) != 0) {
$Errors[] = NoCompanyRecord;
}
$HeaderSQL = "SELECT custbranch.area,\n\t\t\t\t\t\t\t custbranch.taxgroupid,\n\t\t\t\t\t\t\t debtorsmaster.currcode,\n\t\t\t\t\t\t\t rate,\n\t\t\t\t\t\t\t salesman\n\t\t\t\t\t\t\tFROM debtorsmaster\n\t\t\t\t\t\t\tINNER JOIN custbranch\n\t\t\t\t\t\t\tON debtorsmaster.debtorno = custbranch.debtorno\n\t\t\t\t\t\t\tINNER JOIN currencies\n\t\t\t\t\t\t\tON debtorsmaster.currcode=currencies.currabrev\n\t\t\t\t\t\t\tWHERE custbranch.debtorno = '" . $Header['debtorno'] . "'\n\t\t\t\t\t\t\tAND custbranch.branchcode='" . $Header['branchcode'] . "'";
$HeaderResult = api_DB_query($HeaderSQL, $db);
if (DB_error_no($db) != 0) {
$Errors[] = NoReadCustomerBranch;
}
$CN_Header = DB_fetch_array($HeaderResult);
$TaxProvResult = api_DB_query("SELECT taxprovinceid FROM locations WHERE loccode='" . $Header['fromstkloc'] . "'", $db);
if (DB_error_no($db) != 0) {
$Errors[] = NoTaxProvince;
}
$myrow = DB_fetch_row($TaxProvResult);
$DispTaxProvinceID = $myrow[0];
/*Start an SQL transaction */
$result = DB_Txn_Begin($db);
/*Now Get the next credit note number - function in SQL_CommonFunctions*/
$CreditNoteNo = GetNextTransNo(11, $db);
$PeriodNo = GetCurrentPeriod($db);
$TotalFXNetCredit = 0;
$TotalFXTax = 0;
$TaxTotals = array();
$LineCounter = 0;
foreach ($LineDetails as $CN_Line) {
$LineSQL = "SELECT taxcatid,\n\t\t\t\t\t\t\t\tmbflag,\n\t\t\t\t\t\t\t\tmaterialcost+labourcost+overheadcost AS standardcost\n\t\t\t\t\t\tFROM stockmaster\n\t\t\t\t\t\tWHERE stockid ='" . $CN_Line['stockid'] . "'";
$LineResult = api_DB_query($LineSQL, $db);
if (DB_error_no($db) != 0 or DB_num_rows($LineResult) == 0) {
$Errors[] = NoReadItem;
return $Errors;
}
$LineRow = DB_fetch_array($LineResult);
$StandardCost = $LineRow['standardcost'];
$LocalCurrencyPrice = $CN_Line['price'] * (1 - floatval($CN_Line['discountpercent'])) / $CN_Header['rate'];
$LineNetAmount = $CN_Line['price'] * $CN_Line['qty'] * (1 - floatval($CN_Line['discountpercent']));
/*Gets the Taxes and rates applicable to this line from the TaxGroup of the branch and TaxCategory of the item
and the taxprovince of the dispatch location */
$SQL = "SELECT taxgrouptaxes.calculationorder,\n\t\t\t\t\t\t\ttaxauthorities.description,\n\t\t\t\t\t\t\ttaxgrouptaxes.taxauthid,\n\t\t\t\t\t\t\ttaxauthorities.taxglcode,\n\t\t\t\t\t\t\ttaxgrouptaxes.taxontax,\n\t\t\t\t\t\t\ttaxauthrates.taxrate\n\t\t\t\t\tFROM taxauthrates INNER JOIN taxgrouptaxes ON\n\t\t\t\t\t\ttaxauthrates.taxauthority=taxgrouptaxes.taxauthid\n\t\t\t\t\t\tINNER JOIN taxauthorities ON\n\t\t\t\t\t\ttaxauthrates.taxauthority=taxauthorities.taxid\n\t\t\t\t\tWHERE taxgrouptaxes.taxgroupid='" . $CN_Header['taxgroupid'] . "'\n\t\t\t\t\tAND taxauthrates.dispatchtaxprovince='" . $DispTaxProvinceID . "'\n\t\t\t\t\tAND taxauthrates.taxcatid = '" . $LineRow['taxcatid'] . "'\n\t\t\t\t\tORDER BY taxgrouptaxes.calculationorder";
$GetTaxRatesResult = api_DB_query($SQL, $db);
if (DB_error_no($db) != 0) {
$Errors[] = TaxRatesFailed;
}
$LineTaxAmount = 0;
while ($myrow = DB_fetch_array($GetTaxRatesResult)) {
if (!isset($TaxTotals[$myrow['taxauthid']]['FXAmount'])) {
$TaxTotals[$myrow['taxauthid']]['FXAmount'] = 0;
}
$TaxAuthID = $myrow['taxauthid'];
$TaxTotals[$myrow['taxauthid']]['GLCode'] = $myrow['taxglcode'];
$TaxTotals[$myrow['taxauthid']]['TaxRate'] = $myrow['taxrate'];
$TaxTotals[$myrow['taxauthid']]['TaxAuthDescription'] = $myrow['description'];
if ($myrow['taxontax'] == 1) {
$TaxAuthAmount = ($LineNetAmount + $LineTaxAmount) * $myrow['taxrate'];
} else {
$TaxAuthAmount = $LineNetAmount * $myrow['taxrate'];
}
$TaxTotals[$myrow['taxauthid']]['FXAmount'] += $TaxAuthAmount;
/*Make an array of the taxes and amounts including GLcodes for later posting - need debtortransid
so can only post once the debtor trans is posted - can only post debtor trans when all tax is calculated */
$LineTaxes[$LineCounter][$myrow['calculationorder']] = array('TaxCalculationOrder' => $myrow['calculationorder'], 'TaxAuthID' => $myrow['taxauthid'], 'TaxAuthDescription' => $myrow['description'], 'TaxRate' => $myrow['taxrate'], 'TaxOnTax' => $myrow['taxontax'], 'TaxAuthAmount' => $TaxAuthAmount);
$LineTaxAmount += $TaxAuthAmount;
}
//end loop around Taxes
$TotalFXNetCredit += $LineNetAmount;
$TotalFXTax += $LineTaxAmount;
if ($LineRow['mbflag'] == 'B' or $LineRow['mbflag'] == 'M') {
$Assembly = False;
/* Need to get the current location quantity
will need it later for the stock movement */
$SQL = "SELECT locstock.quantity\n\t\t\t\t\t\tFROM locstock\n\t\t\t\t\t\tWHERE locstock.stockid='" . $CN_Line['stockid'] . "'\n\t\t\t\t\t\tAND loccode= '" . $Header['fromstkloc'] . "'";
$Result = api_DB_query($SQL, $db);
if (DB_num_rows($Result) == 1) {
$LocQtyRow = DB_fetch_row($Result);
$QtyOnHandPrior = $LocQtyRow[0];
} else {
/* There must be some error this should never happen */
$QtyOnHandPrior = 0;
}
$SQL = "UPDATE locstock\n\t\t\t\t\t\tSET quantity = locstock.quantity - " . $CN_Line['qty'] . "\n\t\t\t\t\t\tWHERE locstock.stockid = '" . $CN_Line['stockid'] . "'\n\t\t\t\t\t\tAND loccode = '" . $Header['fromstkloc'] . "'";
$Result = api_DB_query($SQL, $db, '', '', true);
$SQL = "INSERT INTO stockmoves (stockid,\n\t\t\t\t\t\t\t\t\t\t\t\ttype,\n\t\t\t\t\t\t\t\t\t\t\t\ttransno,\n\t\t\t\t\t\t\t\t\t\t\t\tloccode,\n\t\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\t\tdebtorno,\n\t\t\t\t\t\t\t\t\t\t\t\tbranchcode,\n\t\t\t\t\t\t\t\t\t\t\t\tprice,\n\t\t\t\t\t\t\t\t\t\t\t\tprd,\n\t\t\t\t\t\t\t\t\t\t\t\treference,\n\t\t\t\t\t\t\t\t\t\t\t\tqty,\n\t\t\t\t\t\t\t\t\t\t\t\tdiscountpercent,\n\t\t\t\t\t\t\t\t\t\t\t\tstandardcost,\n\t\t\t\t\t\t\t\t\t\t\t\tnewqoh)\n\t\t\t\t\t\tVALUES ('" . $CN_Line['stockid'] . "',\n\t\t\t\t\t\t\t\t'11',\n\t\t\t\t\t\t\t\t'" . $CreditNoteNo . "',\n\t\t\t\t\t\t\t\t'" . $Header['fromstkloc'] . "',\n\t\t\t\t\t\t\t\t'" . $Header['trandate'] . "',\n\t\t\t\t\t\t\t\t'" . $Header['debtorno'] . "',\n\t\t\t\t\t\t\t\t'" . $Header['branchcode'] . "',\n\t\t\t\t\t\t\t\t'" . $LocalCurrencyPrice . "',\n\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t'" . $Header['customerref'] . "',\n\t\t\t\t\t\t\t\t'" . -$CN_Line['qty'] . "',\n\t\t\t\t\t\t\t\t'" . $CN_Line['discountpercent'] . "',\n\t\t\t\t\t\t\t\t'" . $StandardCost . "',\n\t\t\t\t\t\t\t\t'" . ($QtyOnHandPrior - $CN_Line['qty']) . "' )";
$Result = api_DB_query($SQL, $db, '', '', true);
} else {
if ($LineRow['mbflag'] == 'A') {
/* its an assembly */
/*Need to get the BOM for this part and make
stock moves for the components then update the Location stock balances */
$Assembly = True;
$StandardCost = 0;
/*To start with - accumulate the cost of the comoponents for use in journals later on */
$SQL = "SELECT bom.component,\n\t\t\t\t\t\t\t\tbom.quantity,\n\t\t\t\t\t\t\t\tstockmaster.materialcost+stockmaster.labourcost+stockmaster.overheadcost AS standard\n\t\t\t\t\t\t\tFROM bom INNER JOIN stockmaster\n\t\t\t\t\t\t\tON bom.component=stockmaster.stockid\n\t\t\t\t\t\t\tWHERE bom.parent='" . $CN_Line['stockid'] . "'\n\t\t\t\t\t\t\tAND bom.effectiveto >= '" . Date('Y-m-d') . "'\n\t\t\t\t\t\t\tAND bom.effectiveafter < '" . Date('Y-m-d') . "'";
$AssResult = api_DB_query($SQL, $db);
while ($AssParts = DB_fetch_array($AssResult, $db)) {
$StandardCost += $AssParts['standard'] * $AssParts['quantity'];
/* Need to get the current location quantity
will need it later for the stock movement */
$SQL = "SELECT locstock.quantity\n\t\t\t\t\t\t\tFROM locstock\n\t\t\t\t\t\t\tWHERE locstock.stockid='" . $AssParts['component'] . "'\n\t\t\t\t\t\t\tAND loccode= '" . $Header['fromstkloc'] . "'";
$Result = api_DB_query($SQL, $db);
if (DB_num_rows($Result) == 1) {
$LocQtyRow = DB_fetch_row($Result);
$QtyOnHandPrior = $LocQtyRow[0];
} else {
/*There must be some error this should never happen */
$QtyOnHandPrior = 0;
}
if (empty($AssParts['standard'])) {
$AssParts['standard'] = 0;
}
$SQL = "INSERT INTO stockmoves (stockid,\n\t\t\t\t\t\t\t\t\t\t\t\t\ttype,\n\t\t\t\t\t\t\t\t\t\t\t\t\ttransno,\n\t\t\t\t\t\t\t\t\t\t\t\t\tloccode,\n\t\t\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\t\t\tdebtorno,\n\t\t\t\t\t\t\t\t\t\t\t\t\tbranchcode,\n\t\t\t\t\t\t\t\t\t\t\t\t\tprd,\n\t\t\t\t\t\t\t\t\t\t\t\t\treference,\n\t\t\t\t\t\t\t\t\t\t\t\t\tqty,\n\t\t\t\t\t\t\t\t\t\t\t\t\tstandardcost,\n\t\t\t\t\t\t\t\t\t\t\t\t\tshow_on_inv_crds,\n\t\t\t\t\t\t\t\t\t\t\t\t\tnewqoh)\n\t\t\t\t\t\t\t\t\t\tVALUES ('" . $AssParts['component'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t 11,\n\t\t\t\t\t\t\t\t\t\t\t\t '" . $CreditNoteNo . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . $Header['fromstkloc'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . $Header['trandate'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . $Header['debtorno'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . $Header['branchcode'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . _('Assembly') . ': ' . $CN_Line['stockid'] . ' ' . $Header['customerref'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . -$AssParts['quantity'] * $CN_Line['qty'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . $AssParts['standard'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t 0,\n\t\t\t\t\t\t\t\t\t\t\t\t '" . ($QtyOnHandPrior - $AssParts['quantity'] * $CN_Line['qty']) . "'\t)";
$Result = DB_query($SQL, $db, '', '', true);
$SQL = "UPDATE locstock\n\t\t\t\t\t\t\tSET quantity = locstock.quantity - " . $AssParts['quantity'] * $CN_Line['qty'] . "\n\t\t\t\t\t\t\tWHERE locstock.stockid = '" . $AssParts['component'] . "'\n\t\t\t\t\t\t\tAND loccode = '" . $Header['fromlocstk'] . "'";
$Result = DB_query($SQL, $db, '', '', true);
}
/* end of assembly explosion and updates */
}
}
/* end of its an assembly */
if ($LineRow['mbflag'] == 'A' or $LineRow['mbflag'] == 'D') {
/*it's a Dummy/Service item or an Assembly item - still need stock movement record
* but quantites on hand are always nil */
$SQL = "INSERT INTO stockmoves (stockid,\n\t\t\t\t\t\t\t\t\t\t\t\ttype,\n\t\t\t\t\t\t\t\t\t\t\t\ttransno,\n\t\t\t\t\t\t\t\t\t\t\t\tloccode,\n\t\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\t\tdebtorno,\n\t\t\t\t\t\t\t\t\t\t\t\tbranchcode,\n\t\t\t\t\t\t\t\t\t\t\t\tprice,\n\t\t\t\t\t\t\t\t\t\t\t\tprd,\n\t\t\t\t\t\t\t\t\t\t\t\treference,\n\t\t\t\t\t\t\t\t\t\t\t\tqty,\n\t\t\t\t\t\t\t\t\t\t\t\tdiscountpercent,\n\t\t\t\t\t\t\t\t\t\t\t\tstandardcost,\n\t\t\t\t\t\t\t\t\t\t\t\tnewqoh)\n\t\t\t\t\t\tVALUES ('" . $CN_Line['stockid'] . "',\n\t\t\t\t\t\t\t\t'11',\n\t\t\t\t\t\t\t\t'" . $CreditNoteNo . "',\n\t\t\t\t\t\t\t\t'" . $Header['fromstkloc'] . "',\n\t\t\t\t\t\t\t\t'" . $Header['trandate'] . "',\n\t\t\t\t\t\t\t\t'" . $Header['debtorno'] . "',\n\t\t\t\t\t\t\t\t'" . $Header['branchcode'] . "',\n\t\t\t\t\t\t\t\t'" . $LocalCurrencyPrice . "',\n\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t'" . $Header['customerref'] . "',\n\t\t\t\t\t\t\t\t'" . -$CN_Line['qty'] . "',\n\t\t\t\t\t\t\t\t'" . $CN_Line['discountpercent'] . "',\n\t\t\t\t\t\t\t\t'" . $StandardCost . "',\n\t\t\t\t\t\t\t\t'0' )";
$Result = api_DB_query($SQL, $db, '', '', true);
}
/*Get the ID of the StockMove... */
$StkMoveNo = DB_Last_Insert_ID($db, 'stockmoves', 'stkmoveno');
/*Insert the taxes that applied to this line */
foreach ($LineTaxes[$LineCounter] as $Tax) {
$SQL = "INSERT INTO stockmovestaxes (stkmoveno,\n\t\t\t\t\t\t\t\t\ttaxauthid,\n\t\t\t\t\t\t\t\t\ttaxrate,\n\t\t\t\t\t\t\t\t\ttaxcalculationorder,\n\t\t\t\t\t\t\t\t\ttaxontax)\n\t\t\t\t\t\tVALUES ('" . $StkMoveNo . "',\n\t\t\t\t\t\t\t'" . $Tax['TaxAuthID'] . "',\n\t\t\t\t\t\t\t'" . $Tax['TaxRate'] . "',\n\t\t\t\t\t\t\t'" . $Tax['TaxCalculationOrder'] . "',\n\t\t\t\t\t\t\t'" . $Tax['TaxOnTax'] . "')";
$Result = DB_query($SQL, $db, '', '', true);
}
/*Insert Sales Analysis records */
$SQL = "SELECT COUNT(*),\n\t\t\t\t\t\tsalesanalysis.stkcategory,\n\t\t\t\t\t\tsalesanalysis.area,\n\t\t\t\t\t\tsalesanalysis.salesperson,\n\t\t\t\t\t\tsalesanalysis.periodno,\n\t\t\t\t\t\tsalesanalysis.typeabbrev,\n\t\t\t\t\t\tsalesanalysis.cust,\n\t\t\t\t\t\tsalesanalysis.custbranch,\n\t\t\t\t\t\tsalesanalysis.stockid\n\t\t\t\t\tFROM salesanalysis,\n\t\t\t\t\t\tcustbranch,\n\t\t\t\t\t\tstockmaster\n\t\t\t\t\tWHERE salesanalysis.stkcategory=stockmaster.categoryid\n\t\t\t\t\tAND salesanalysis.stockid=stockmaster.stockid\n\t\t\t\t\tAND salesanalysis.cust=custbranch.debtorno\n\t\t\t\t\tAND salesanalysis.custbranch=custbranch.branchcode\n\t\t\t\t\tAND salesanalysis.area=custbranch.area\n\t\t\t\t\tAND salesanalysis.salesperson=custbranch.salesman\n\t\t\t\t\tAND salesanalysis.typeabbrev ='" . $Header['tpe'] . "'\n\t\t\t\t\tAND salesanalysis.periodno='" . $PeriodNo . "'\n\t\t\t\t\tAND salesanalysis.cust " . LIKE . " '" . $Header['debtorno'] . "'\n\t\t\t\t\tAND salesanalysis.custbranch " . LIKE . " '" . $Header['branchcode'] . "'\n\t\t\t\t\tAND salesanalysis.stockid " . LIKE . " '" . $CN_Line['stockid'] . "'\n\t\t\t\t\tAND salesanalysis.budgetoractual='1'\n\t\t\t\t\tGROUP BY salesanalysis.stockid,\n\t\t\t\t\t\tsalesanalysis.stkcategory,\n\t\t\t\t\t\tsalesanalysis.cust,\n\t\t\t\t\t\tsalesanalysis.custbranch,\n\t\t\t\t\t\tsalesanalysis.area,\n\t\t\t\t\t\tsalesanalysis.periodno,\n\t\t\t\t\t\tsalesanalysis.typeabbrev,\n\t\t\t\t\t\tsalesanalysis.salesperson";
$Result = api_DB_query($SQL, $db, '', '', true);
$myrow = DB_fetch_row($Result);
if ($myrow[0] > 0) {
/*Update the existing record that already exists */
$SQL = "UPDATE salesanalysis\n\t\t\t\t\t\tSET amt=amt+" . $CN_Line['price'] * $CN_Line['qty'] / $CN_Header['rate'] . ",\n\t\t\t\t\t\tqty=qty +" . $CN_Line['qty'] . ",\n\t\t\t\t\t\tdisc=disc+" . $CN_Line['discountpercent'] * $CN_Line['price'] * $CN_Line['qty'] / $CN_Header['rate'] . "\n\t\t\t\t\t\tWHERE salesanalysis.area='" . $myrow[2] . "'\n\t\t\t\t\t\tAND salesanalysis.salesperson='" . $myrow[3] . "'\n\t\t\t\t\t\tAND typeabbrev ='" . $Header['tpe'] . "'\n\t\t\t\t\t\tAND periodno = '" . $PeriodNo . "'\n\t\t\t\t\t\tAND cust " . LIKE . " '" . $Header['debtorno'] . "'\n\t\t\t\t\t\tAND custbranch " . LIKE . " '" . $Header['branchcode'] . "'\n\t\t\t\t\t\tAND stockid " . LIKE . " '" . $CN_Line['stockid'] . "'\n\t\t\t\t\t\tAND salesanalysis.stkcategory ='" . $myrow[1] . "'\n\t\t\t\t\t\tAND budgetoractual='1'";
} else {
/* insert a new sales analysis record */
$SQL = "INSERT INTO salesanalysis (\ttypeabbrev,\n\t\t\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\t\t\tamt,\n\t\t\t\t\t\t\t\t\t\t\t\t\tcost,\n\t\t\t\t\t\t\t\t\t\t\t\t\tcust,\n\t\t\t\t\t\t\t\t\t\t\t\t\tcustbranch,\n\t\t\t\t\t\t\t\t\t\t\t\t\tqty,\n\t\t\t\t\t\t\t\t\t\t\t\t\tdisc,\n\t\t\t\t\t\t\t\t\t\t\t\t\tstockid,\n\t\t\t\t\t\t\t\t\t\t\t\t\tarea,\n\t\t\t\t\t\t\t\t\t\t\t\t\tbudgetoractual,\n\t\t\t\t\t\t\t\t\t\t\t\t\tsalesperson,\n\t\t\t\t\t\t\t\t\t\t\t\t\tstkcategory )\n\t\t\t\t\t\t\t\tSELECT '" . $Header['tpe'] . "',\n\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t'" . $CN_Line['price'] * $CN_Line['qty'] / $CN_Header['rate'] . "',\n\t\t\t\t\t\t\t\t\t0,\n\t\t\t\t\t\t\t\t\t'" . $Header['debtorno'] . "',\n\t\t\t\t\t\t\t\t\t'" . $Header['branchcode'] . "',\n\t\t\t\t\t\t\t\t\t'" . $CN_Line['qty'] . "',\n\t\t\t\t\t\t\t\t\t'" . $CN_Line['discountpercent'] * $CN_Line['price'] * $CN_Line['qty'] / $CN_Header['rate'] . "',\n\t\t\t\t\t\t\t\t\t'" . $CN_Line['stockid'] . "',\n\t\t\t\t\t\t\t\t\tcustbranch.area,\n\t\t\t\t\t\t\t\t\t1,\n\t\t\t\t\t\t\t\t\tcustbranch.salesman,\n\t\t\t\t\t\t\t\t\tstockmaster.categoryid\n\t\t\t\t\t\t\t\tFROM stockmaster, custbranch\n\t\t\t\t\t\t\t\tWHERE stockmaster.stockid = '" . $CN_Line['stockid'] . "'\n\t\t\t\t\t\t\t\tAND custbranch.debtorno = '" . $Header['debtorno'] . "'\n\t\t\t\t\t\t\t\tAND custbranch.branchcode='" . $Header['branchcode'] . "'";
}
$Result = api_DB_query($SQL, $db, '', '', true);
if ($CompanyRecord['gllink_stock'] == 1 and $StandardCost != 0) {
/*first the cost of sales entry - GL accounts are retrieved using the function GetCOGSGLAccount from includes/GetSalesTransGLCodes.inc */
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\t\t\t\tVALUES (11,\n\t\t\t\t\t\t\t\t\t\t'" . $CreditNoteNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $Header['trandate'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . GetCOGSGLAccount($CN_Header['area'], $CN_Line['stockid'], $Header['tpe'], $db) . "',\n\t\t\t\t\t\t\t\t\t\t'" . $Header['debtorno'] . " - " . $CN_Line['stockid'] . " x " . $CN_Line['qty'] . " @ " . $StandardCost . "',\n\t\t\t\t\t\t\t\t\t\t'" . $StandardCost * $CN_Line['qty'] . "')";
$Result = api_DB_query($SQL, $db, '', '', true);
/*now the stock entry - this is set to the cost act in the case of a fixed asset disposal */
$StockGLCode = GetStockGLCode($CN_Line['stockid'], $db);
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\t\t\t\tVALUES (11,\n\t\t\t\t\t\t\t\t\t\t'" . $CreditNoteNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $Header['trandate'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $StockGLCode['stockact'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $Header['debtorno'] . " - " . $CN_Line['stockid'] . " x " . $CN_Line['qty'] . " @ " . $StandardCost . "',\n\t\t\t\t\t\t\t\t\t\t'" . -$StandardCost * $CN_Line['qty'] . "')";
$Result = api_DB_query($SQL, $db, '', '', true);
}
/* end of if GL and stock integrated and standard cost !=0 and not an asset */
if ($CompanyRecord['gllink_debtors'] == 1 and $CN_Line['price'] != 0) {
//Post sales transaction to GL credit sales
$SalesGLAccounts = GetSalesGLAccount($CN_Header['area'], $CN_Line['stockid'], $Header['tpe'], $db);
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\tamount )\n\t\t\t\t\tVALUES ('11',\n\t\t\t\t\t\t'" . $CreditNoteNo . "',\n\t\t\t\t\t\t'" . $Header['trandate'] . "',\n\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t'" . $SalesGLAccounts['salesglcode'] . "',\n\t\t\t\t\t\t'" . $Header['debtorno'] . " - " . $CN_Line['stockid'] . " x " . $CN_Line['qty'] . " @ " . $CN_Line['price'] . "',\n\t\t\t\t\t\t'" . -$CN_Line['price'] * $CN_Line['qty'] / $CN_Header['rate'] . "'\n\t\t\t\t\t)";
$Result = api_DB_query($SQL, $db, '', '', true);
if ($CN_Line['discountpercent'] != 0) {
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\t\tVALUES (11,\n\t\t\t\t\t\t\t\t'" . $CreditNoteNo . "',\n\t\t\t\t\t\t\t\t'" . $Header['trandate'] . "',\n\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t'" . $SalesGLAccounts['discountglcode'] . "',\n\t\t\t\t\t\t\t\t'" . $Header['debtorno'] . " - " . $CN_Line['stockid'] . " @ " . $CN_Line['discountpercent'] * 100 . "%',\n\t\t\t\t\t\t\t\t'" . $CN_Line['price'] * $CN_Line['qty'] * $CN_Line['discountpercent'] / $CN_Header['rate'] . "')";
$Result = DB_query($SQL, $db, '', '', true);
}
/*end of if discount !=0 */
}
/*end of if sales integrated with gl */
$LineCounter++;
//needed for the array of taxes by line
}
/*end of OrderLine loop */
$TotalCreditLocalCurr = ($TotalFXNetCredit + $TotalFXTax) / $CN_Header['rate'];
if ($CompanyRecord['gllink_debtors'] == 1) {
/*Now post the tax to the GL at local currency equivalent */
if ($CompanyRecord['gllink_debtors'] == 1 and $TaxAuthAmount != 0) {
/*Loop through the tax authorities array to post each total to the taxauth glcode */
foreach ($TaxTotals as $Tax) {
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\t\tamount )\n\t\t\t\t\t\t\t\t\t\t\tVALUES (11,\n\t\t\t\t\t\t\t\t\t\t\t'" . $CreditNoteNo . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $Header['trandate'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $Tax['GLCode'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $Header['debtorno'] . "-" . $Tax['TaxAuthDescription'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . -$Tax['FXAmount'] / $CN_Header['rate'] . "' )";
$Result = api_DB_query($SQL, $db, '', '', true);
}
}
/*Post debtors transaction to GL credit debtors, and debit sales */
if ($TotalCreditLocalCurr != 0) {
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\t\t\t\tVALUES ('11',\n\t\t\t\t\t\t\t\t\t\t'" . $CreditNoteNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $Header['trandate'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $CompanyRecord['debtorsact'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $Header['debtorno'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $TotalCreditLocalCurr . "')";
$Result = api_DB_query($SQL, $db, '', '', true);
}
EnsureGLEntriesBalance(11, $CreditNoteNo, $db);
}
/*end of if Sales and GL integrated */
/*Now insert the DebtorTrans */
$SQL = "INSERT INTO debtortrans (transno,\n\t\t\t\t\t\t\t\t\t\ttype,\n\t\t\t\t\t\t\t\t\t\tdebtorno,\n\t\t\t\t\t\t\t\t\t\tbranchcode,\n\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\tinputdate,\n\t\t\t\t\t\t\t\t\t\tprd,\n\t\t\t\t\t\t\t\t\t\treference,\n\t\t\t\t\t\t\t\t\t\ttpe,\n\t\t\t\t\t\t\t\t\t\tovamount,\n\t\t\t\t\t\t\t\t\t\tovgst,\n\t\t\t\t\t\t\t\t\t\trate,\n\t\t\t\t\t\t\t\t\t\tshipvia)\n\t\t\t\t\t\t\t\t\tVALUES (\n\t\t\t\t\t\t\t\t\t\t'" . $CreditNoteNo . "',\n\t\t\t\t\t\t\t\t\t\t11,\n\t\t\t\t\t\t\t\t\t\t'" . $Header['debtorno'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $Header['branchcode'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $Header['trandate'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . date('Y-m-d H-i-s') . "',\n\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $Header['customerref'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $Header['tpe'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $TotalFXNetCredit . "',\n\t\t\t\t\t\t\t\t\t\t'" . $TotalFXTax . "',\n\t\t\t\t\t\t\t\t\t\t'" . $CN_Header['rate'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $Header['shipvia'] . "')";
$Result = api_DB_query($SQL, $db, '', '', true);
$DebtorTransID = DB_Last_Insert_ID($db, 'debtortrans', 'id');
/*for each Tax - need to insert into debtortranstaxes */
foreach ($TaxTotals as $TaxAuthID => $Tax) {
$SQL = "INSERT INTO debtortranstaxes (debtortransid,\n\t\t\t\t\t\t\t\t\t\t\t\ttaxauthid,\n\t\t\t\t\t\t\t\t\t\t\t\ttaxamount)\n\t\t\t\t\t\t\t\tVALUES ('" . $DebtorTransID . "',\n\t\t\t\t\t\t\t\t\t\t'" . $TaxAuthID . "',\n\t\t\t\t\t\t\t\t\t\t'" . $Tax['FXAmount'] / $CN_Header['rate'] . "')";
$Result = api_DB_query($SQL, $db, '', '', true);
}
#Now figure out if there was an invoice in the same POS transaction to allocate against?
$SQL = "SELECT id,\n\t\t\t\t\tovamount+ovgst AS total,\n\t\t\t\t\talloc\n\t\t\t\tFROM debtortrans\n\t\t\t\tWHERE customerref='" . $Header['customerref'] . "'\n\t\t\t\tAND type=10\n\t\t\t\tAND settled=0";
$Result = api_DB_query($SQL, $db, '', '', true);
$TotalCreditFX = $TotalFXNetCredit + $TotalFXTax;
#Should be negative number
$Allocated = 0;
if (DB_num_rows($Result) > 0) {
while ($InvoiceRow = DB_fetch_array($Result) and $Allocated > $TotalCreditFX) {
if ($InvoiceRow['total'] - $InvoiceRow['alloc'] + $TotalCreditFX - $Allocated > 0) {
/*Then we can allocate all of the (remaining) credit against this invoice */
$AllocateAmount = $InvoiceRow['total'] - $InvoiceRow['alloc'] + $TotalCreditFX - $Allocated;
} elseif ($InvoiceRow['total'] - $InvoiceRow['alloc'] - $Allocated > 0) {
$AllocateAmount = $InvoiceRow['total'] - $InvoiceRow['alloc'] - $Allocated;
} else {
$AllocateAmount = 0;
}
if ($AllocateAmount > 0) {
$SQL = "INSERT INTO\tcustallocns (datealloc,\n\t\t\t\t\t\t\t\t\t\t\t\t\t amt,\n\t\t\t\t\t\t\t\t\t\t\t\t\t transid_allocfrom,\n\t\t\t\t\t\t\t\t\t\t\t\t\t transid_allocto)\n\t\t\t\t\t\t\tVALUES ('" . date('Y-m-d') . "',\n\t\t\t\t\t\t\t\t\t'" . $AllocateAmount . "',\n\t\t\t\t\t\t\t\t\t'" . $DebtorTransID . "',\n\t\t\t\t\t\t\t\t\t'" . $InvoiceRow['id'] . "')";
$InsertAllocResult = api_DB_query($SQL, $db, '', '', true);
}
if (abs($InvoiceRow['total'] - $InvoiceRow['alloc'] - $AllocateAmount) < 0.005) {
$Settled = 1;
} else {
$Settled = 0;
}
$SQL = "UPDATE debtortrans SET alloc = alloc + " . $AllocateAmount . ",\n\t\t\t\t\t\t\t\t\t\t\t\tsettled = '" . $Settled . "'\n\t\t\t\t\t\tWHERE id = '" . $InvoiceRow['id'] . "'";
$UpdateAllocResult = api_DB_query($SQL, $db, '', '', true);
$Allocated -= $AllocateAmount;
}
if (abs($TotalCreditFX - $Allocated) < 0.005) {
$Settled = 1;
} else {
$Settled = 0;
}
$SQL = "UPDATE debtortrans SET alloc = alloc + " . $Allocated . ",\n\t\t\t\t\t\t\t\t\t\t\t\tsettled = '" . $Settled . "'\n\t\t\t\t\tWHERE id = '" . $DebtorTransID . "'";
$UpdateAllocResult = api_DB_query($SQL, $db, '', '', true);
}
if (sizeof($Errors) == 0) {
$Result = DB_Txn_Commit($db);
$Errors[0] = 0;
$Errors[1] = $CreditNoteNo;
} else {
$Result = DB_Txn_Rollback($db);
}
return $Errors;
}
/*first reverse credit the cost of sales entry*/
$COGSAccount = GetCOGSGLAccount($Area, $CreditLine->StockID, $_SESSION['CreditItems' . $identifier]->DefaultSalesType, $db);
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\t\t\t\t\tVALUES (\n\t\t\t\t\t\t\t\t\t\t\t11,\n\t\t\t\t\t\t\t\t\t\t\t'" . $CreditNo . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $SQLCreditDate . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $COGSAccount . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['CreditItems' . $identifier]->DebtorNo . " - " . $CreditLine->StockID . " x " . $CreditLine->Quantity . " @ " . $CreditLine->StandardCost . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $CreditLine->StandardCost * -$CreditLine->Quantity . "')";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The cost of the stock credited GL posting could not be inserted because');
$DbgMsg = _('The following SQL to insert the GLTrans record was used');
$Result = DB_query($SQL, $ErrMsg, $DbgMsg, true);
if ($_POST['CreditType'] == 'WriteOff') {
/* The double entry required is to reverse the cost of sales entry as above
then debit the expense account the stock is to written off to */
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\t\t\tVALUES (11,\n\t\t\t\t\t\t\t\t\t\t'" . $CreditNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $SQLCreditDate . "',\n\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_POST['WriteOffGLCode'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['CreditItems' . $identifier]->DebtorNo . " - " . $CreditLine->StockID . " x " . $CreditLine->Quantity . " @ " . $CreditLine->StandardCost . "',\n\t\t\t\t\t\t\t\t\t\t'" . $CreditLine->StandardCost * $CreditLine->Quantity . "'\n\t\t\t\t\t\t\t\t\t\t)";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The cost of the stock credited GL posting could not be inserted because');
$DbgMsg = _('The following SQL to insert the GLTrans record was used');
$Result = DB_query($SQL, $ErrMsg, $DbgMsg, true);
} else {
/*the goods are coming back into stock so debit the stock account*/
$StockGLCode = GetStockGLCode($CreditLine->StockID, $db);
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\t\t\t\t\tVALUES (11,\n\t\t\t\t\t\t\t\t\t\t\t'" . $CreditNo . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $SQLCreditDate . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $StockGLCode['stockact'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['CreditItems' . $identifier]->DebtorNo . " - " . $CreditLine->StockID . " x " . $CreditLine->Quantity . " @ " . $CreditLine->StandardCost . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $CreditLine->StandardCost * $CreditLine->Quantity . "'\n\t\t\t\t\t\t\t\t\t\t\t)";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The stock side (or write off) of the cost of sales GL posting could not be inserted because');
$DbgMsg = _('The following SQL to insert the GLTrans record was used');
$Result = DB_query($SQL, $ErrMsg, $DbgMsg, true);
}
}
/* end of if GL and stock integrated and standard cost !=0 */
if ($_SESSION['CompanyRecord']['gllink_debtors'] == 1 and $CreditLine->Price != 0) {
//Post sales transaction to GL credit sales
$SalesGLAccounts = GetSalesGLAccount($Area, $CreditLine->StockID, $_SESSION['CreditItems' . $identifier]->DefaultSalesType, $db);
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\t\t\t\t\tVALUES (11,\n\t\t\t\t\t\t\t\t\t\t\t'" . $CreditNo . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $SQLCreditDate . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $SalesGLAccounts['salesglcode'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['CreditItems' . $identifier]->DebtorNo . " - " . $CreditLine->StockID . " x " . $CreditLine->Quantity . " @ " . $CreditLine->Price . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $CreditLine->Price * $CreditLine->Quantity / $_SESSION['CurrencyRate'] . "'\n\t\t\t\t\t\t\t\t\t\t\t)";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The credit note GL posting could not be inserted because');
$DbgMsg = _('The following SQL to insert the GLTrans record was used');
$Result = DB_query($SQL, $ErrMsg, $DbgMsg, true);
if ($CreditLine->DiscountPercent != 0) {
$SQL = "INSERT INTO stockmoves (\n\t\t\t\t\t\tstockid,\n\t\t\t\t\t\ttype,\n\t\t\t\t\t\ttransno,\n\t\t\t\t\t\tloccode,\n\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\tdebtorno,\n\t\t\t\t\t\tbranchcode,\n\t\t\t\t\t\tprd,\n\t\t\t\t\t\treference,\n\t\t\t\t\t\tqty,\n\t\t\t\t\t\tprice,\n\t\t\t\t\t\tshow_on_inv_crds,\n\t\t\t\t\t\tnewqoh\n\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t'" . $_SESSION['Items'][$i]['StockID'] . "',\n\t\t\t\t\t\t 10,\n\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t'" . $_SESSION['Items']['Dispensary'] . "',\n\t\t\t\t\t\t'" . FormatDateForSQL($_POST['AdmissionDate']) . "',\n\t\t\t\t\t\t'" . $_POST['PatientNo'] . "',\n\t\t\t\t\t\t'" . $_POST['BranchNo'] . "',\n\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t'" . _('Invoice of Other Medical Service for Patient number') . ' ' . $_POST['PatientNo'] . "',\n\t\t\t\t\t\t'" . -$_SESSION['Items'][$i]['Quantity'] . "',\n\t\t\t\t\t\t'" . $_SESSION['Items'][$i]['Price'] . "',\n\t\t\t\t\t\t1,\n\t\t\t\t\t\t'" . ($QtyOnHandPrior - $_SESSION['Items'][$i]['Quantity']) . "'\n\t\t\t\t\t)";
$BaseStockID = $_SESSION['Items'][$i]['StockID'];
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('Stock movement records for') . ' ' . $_POST['StockID'] . ' ' . _('could not be inserted because');
$DbgMsg = _('The following SQL to insert the stock movement records was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
$SQL = "UPDATE locstock SET quantity = quantity + '" . -$_SESSION['Items'][$i]['Quantity'] . "'\n\t\t\t\t\tWHERE stockid='" . $_SESSION['Items'][$i]['StockID'] . "'\n\t\t\t\t\tAND loccode='" . $_SESSION['Items']['Dispensary'] . "'";
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
/* If GLLink_Stock then insert GLTrans to credit stock and debit cost of sales at standard cost*/
if ($_SESSION['CompanyRecord']['gllink_stock'] == 1 and $_SESSION['Items'][$i]['StandardCost'] != 0) {
/*first the cost of sales entry*/
$SQL = "INSERT INTO gltrans (\ttype,\n\t\t\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\t\t\t\t\t\t\tVALUES (\n\t\t\t\t\t\t\t\t\t\t\t\t\t10,\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . FormatDateForSQL($_POST['AdmissionDate']) . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . GetCOGSGLAccount('AN', $_SESSION['Items'][$i]['StockID'], $_POST['PriceList'], $db) . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $_POST['PatientNo'] . " - " . $_SESSION['Items'][$i]['StockID'] . " x " . $_SESSION['Items'][$i]['Quantity'] . " @ " . $_SESSION['Items'][$i]['StandardCost'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . filter_currency_input($_SESSION['Items'][$i]['StandardCost'] * $_SESSION['Items'][$i]['Quantity']) . "')";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The cost of sales GL posting could not be inserted because');
$DbgMsg = _('The following SQL to insert the GLTrans record was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
/*now the stock entry*/
$StockGLCode = GetStockGLCode($_SESSION['Items'][$i]['StockID'], $db);
$SQL = "INSERT INTO gltrans (\ttype,\n\t\t\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\t\t\tamount )\n\t\t\t\t\t\t\t\t\t\t\t\tVALUES (\n\t\t\t\t\t\t\t\t\t\t\t\t\t10,\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . FormatDateForSQL($_POST['AdmissionDate']) . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $StockGLCode['stockact'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $_POST['PatientNo'] . " - " . $_SESSION['Items'][$i]['StockID'] . " x " . $_SESSION['Items'][$i]['Quantity'] . " @ " . $_SESSION['Items'][$i]['StandardCost'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . filter_currency_input(-$_SESSION['Items'][$i]['StandardCost'] * $_SESSION['Items'][$i]['Quantity']) . "')";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The stock side of the cost of sales GL posting could not be inserted because');
$DbgMsg = _('The following SQL to insert the GLTrans record was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
}
/* end of if GL and stock integrated and standard cost !=0 */
}
/*Insert Sales Analysis records */
if (isset($_SESSION['Items'][$i]['StockID']) and $_SESSION['DispenseOnBill'] == 1) {
$SQL = "SELECT COUNT(*),\n\t\t\t\t\tsalesanalysis.stockid,\n\t\t\t\t\tsalesanalysis.stkcategory,\n\t\t\t\t\tsalesanalysis.cust,\n\t\t\t\t\tsalesanalysis.custbranch,\n\t\t\t\t\tsalesanalysis.area,\n\t\t\t\t\tsalesanalysis.periodno,\n\t\t\t\t\tsalesanalysis.typeabbrev,\n\t\t\t\t\tsalesanalysis.salesperson\n\t\t\t\tFROM salesanalysis,\n\t\t\t\t\tcustbranch,\n\t\t\t\t\tstockmaster\n\t\t\t\tWHERE salesanalysis.stkcategory=stockmaster.categoryid\n\t\t\t\tAND salesanalysis.stockid=stockmaster.stockid\n\t\t\t\tAND salesanalysis.cust=custbranch.debtorno\n\t\t\t\tAND salesanalysis.custbranch=custbranch.branchcode\n\t\t\t\tAND salesanalysis.area=custbranch.area\n\t\t\t\tAND salesanalysis.salesperson=custbranch.salesman\n\t\t\t\tAND salesanalysis.typeabbrev ='" . $_POST['PriceList'] . "'\n\t\t\t\tAND salesanalysis.periodno='" . $PeriodNo . "'\n\t\t\t\tAND salesanalysis.cust " . LIKE . " '" . $_POST['PatientNo'] . "'\n\t\t\t\tAND salesanalysis.custbranch " . LIKE . " '" . $_POST['BranchNo'] . "'\n\t\t\t\tAND salesanalysis.stockid " . LIKE . " '" . $_SESSION['Items'][$i]['StockID'] . "'\n\t\t\t\tAND salesanalysis.budgetoractual=1\n\t\t\t\tGROUP BY salesanalysis.stockid,\n\t\t\t\t\tsalesanalysis.stkcategory,\n\t\t\t\t\tsalesanalysis.cust,\n\t\t\t\t\tsalesanalysis.custbranch,\n\t\t\t\t\tsalesanalysis.area,\n\t\t\t\t\tsalesanalysis.periodno,\n\t\t\t\t\tsalesanalysis.typeabbrev,\n\t\t\t\t\tsalesanalysis.salesperson";
$ErrMsg = _('The count of existing Sales analysis records could not run because');
$DbgMsg = _('SQL to count the no of sales analysis records');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
$myrow = DB_fetch_array($Result);
if ($myrow[0] > 0) {
}
/* now insert the serial stock movement */
$SQL = "INSERT INTO stockserialmoves (stockmoveno,\n\t\t\t\t\t\t\t\t\t\t\t\t\tstockid,\n\t\t\t\t\t\t\t\t\t\t\t\t\tserialno,\n\t\t\t\t\t\t\t\t\t\t\t\t\tmoveqty)\n\t\t\t\t\t\t\t\t\t\tVALUES ('" . $StkMoveNo . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['Adjustment' . $identifier]->StockID . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $Item->BundleRef . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $Item->BundleQty . "')";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The serial stock movement record could not be inserted because');
$DbgMsg = _('The following SQL to insert the serial stock movement records was used');
$Result = DB_query($SQL, $ErrMsg, $DbgMsg, true);
}
/* foreach controlled item in the serialitems array */
}
/*end if the adjustment item is a controlled item */
$SQL = "UPDATE locstock SET quantity = quantity + " . floatval($_SESSION['Adjustment' . $identifier]->Quantity) . "\n\t\t\t\tWHERE stockid='" . $_SESSION['Adjustment' . $identifier]->StockID . "'\n\t\t\t\tAND loccode='" . $_SESSION['Adjustment' . $identifier]->StockLocation . "'";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The location stock record could not be updated because');
$DbgMsg = _('The following SQL to update the stock record was used');
$Result = DB_query($SQL, $ErrMsg, $DbgMsg, true);
if ($_SESSION['CompanyRecord']['gllink_stock'] == 1 and $_SESSION['Adjustment' . $identifier]->StandardCost > 0) {
$StockGLCodes = GetStockGLCode($_SESSION['Adjustment' . $identifier]->StockID, $db);
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\tamount,\n\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\ttag)\n\t\t\t\t\t\t\t\tVALUES (17,\n\t\t\t\t\t\t\t\t\t'" . $AdjustmentNumber . "',\n\t\t\t\t\t\t\t\t\t'" . $SQLAdjustmentDate . "',\n\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t'" . $StockGLCodes['adjglact'] . "',\n\t\t\t\t\t\t\t\t\t'" . round($_SESSION['Adjustment' . $identifier]->StandardCost * -$_SESSION['Adjustment' . $identifier]->Quantity, $_SESSION['CompanyRecord']['decimalplaces']) . "',\n\t\t\t\t\t\t\t\t\t'" . $_SESSION['Adjustment' . $identifier]->StockID . " x " . $_SESSION['Adjustment' . $identifier]->Quantity . " @ " . $_SESSION['Adjustment' . $identifier]->StandardCost . " " . $_SESSION['Adjustment' . $identifier]->Narrative . "',\n\t\t\t\t\t\t\t\t\t'" . $_SESSION['Adjustment' . $identifier]->tag . "')";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The general ledger transaction entries could not be added because');
$DbgMsg = _('The following SQL to insert the GL entries was used');
$Result = DB_query($SQL, $ErrMsg, $DbgMsg, true);
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\tamount,\n\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\ttag)\n\t\t\t\t\t\t\t\tVALUES (17,\n\t\t\t\t\t\t\t\t\t'" . $AdjustmentNumber . "',\n\t\t\t\t\t\t\t\t\t'" . $SQLAdjustmentDate . "',\n\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t'" . $StockGLCodes['stockact'] . "',\n\t\t\t\t\t\t\t\t\t'" . round($_SESSION['Adjustment' . $identifier]->StandardCost * $_SESSION['Adjustment' . $identifier]->Quantity, $_SESSION['CompanyRecord']['decimalplaces']) . "',\n\t\t\t\t\t\t\t\t\t'" . $_SESSION['Adjustment' . $identifier]->StockID . ' x ' . $_SESSION['Adjustment' . $identifier]->Quantity . ' @ ' . $_SESSION['Adjustment' . $identifier]->StandardCost . ' ' . $_SESSION['Adjustment' . $identifier]->Narrative . "',\n\t\t\t\t\t\t\t\t\t'" . $_SESSION['Adjustment' . $identifier]->tag . "'\n\t\t\t\t\t\t\t\t\t)";
$Errmsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The general ledger transaction entries could not be added because');
$DbgMsg = _('The following SQL to insert the GL entries was used');
$Result = DB_query($SQL, $ErrMsg, $DbgMsg, true);
}
EnsureGLEntriesBalance(17, $AdjustmentNumber, $db);
$Result = DB_Txn_Commit();
$ConfirmationText = _('A stock adjustment for') . ' ' . $_SESSION['Adjustment' . $identifier]->StockID . ' - ' . $_SESSION['Adjustment' . $identifier]->ItemDescription . ' ' . _('has been created from location') . ' ' . $_SESSION['Adjustment' . $identifier]->StockLocation . ' ' . _('for a quantity of') . ' ' . locale_number_format($_SESSION['Adjustment' . $identifier]->Quantity, $_SESSION['Adjustment' . $identifier]->DecimalPlaces);
prnMsg($ConfirmationText, 'success');
if ($_SESSION['InventoryManagerEmail'] != '') {
$ConfirmationText = $ConfirmationText . ' ' . _('by user') . ' ' . $_SESSION['UserID'] . ' ' . _('at') . ' ' . Date('Y-m-d H:i:s');
$LocQtyRow = DB_fetch_row($Result);
$QtyOnHandPrior = $LocQtyRow[0];
} else {
// There must actually be some error this should never happen
$QtyOnHandPrior = 0;
}
$SQL = "INSERT INTO stockmoves (stockid,\n\t\t\t\t\t\t\t\ttype,\n\t\t\t\t\t\t\t\ttransno,\n\t\t\t\t\t\t\t\tloccode,\n\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\tprd,\n\t\t\t\t\t\t\t\treference,\n\t\t\t\t\t\t\t\tqty,\n\t\t\t\t\t\t\t\tnewqoh)\n\t\t\t\t\t\tVALUES ('" . $myrow['stockid'] . "',\n\t\t\t\t\t\t\t17,\n\t\t\t\t\t\t\t" . $AdjustmentNumber . ",\n\t\t\t\t\t\t\t'" . $myrow['loccode'] . "',\n\t\t\t\t\t\t\t'" . $SQLAdjustmentDate . "',\n\t\t\t\t\t\t\t" . $PeriodNo . ",\n\t\t\t\t\t\t\t'" . _('Inventory Check') . "',\n\t\t\t\t\t\t\t" . $StockQtyDifference . ",\n\t\t\t\t\t\t\t" . ($QtyOnHandPrior + $StockQtyDifference) . "\n\t\t\t\t\t\t)";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The stock movement record cannot be inserted because');
$DbgMsg = _('The following SQL to insert the stock movement record was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
$SQL = "UPDATE locstock\n\t\t\t\t\t\tSET quantity = quantity + " . $StockQtyDifference . "\n\t\t\t\t\t\tWHERE stockid='" . $myrow['stockid'] . "'\n\t\t\t\t\t\tAND loccode='" . $myrow['loccode'] . "'";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The location stock record could not be updated because');
$DbgMsg = _('The following SQL to update the stock record was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
if ($_SESSION['CompanyRecord']['gllink_stock'] == 1 and $myrow['standardcost'] > 0) {
$StockGLCodes = GetStockGLCode($myrow['stockid'], $db);
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The general ledger transaction entries could not be added because');
$DbgMsg = _('The following SQL to insert the GL entries was used');
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\tamount,\n\t\t\t\t\t\t\t\t\tnarrative)\n\t\t\t\t\t\t\tVALUES (17,\n\t\t\t\t\t\t\t\t" . $AdjustmentNumber . ",\n\t\t\t\t\t\t\t\t'" . $SQLAdjustmentDate . "',\n\t\t\t\t\t\t\t\t" . $PeriodNo . ",\n\t\t\t\t\t\t\t\t" . $StockGLCodes['adjglact'] . ",\n\t\t\t\t\t\t\t\t" . $myrow['standardcost'] * -$StockQtyDifference . ",\n\t\t\t\t\t\t\t\t'" . $myrow['stockid'] . " x " . $StockQtyDifference . " @ " . $myrow['standardcost'] . " - " . _('Inventory Check') . "')";
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The general ledger transaction entries could not be added because');
$DbgMsg = _('The following SQL to insert the GL entries was used');
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\tamount,\n\t\t\t\t\t\t\t\t\tnarrative)\n\t\t\t\t\t\t\tVALUES (17,\n\t\t\t\t\t\t\t\t" . $AdjustmentNumber . ",\n\t\t\t\t\t\t\t\t'" . $SQLAdjustmentDate . "',\n\t\t\t\t\t\t\t\t" . $PeriodNo . ",\n\t\t\t\t\t\t\t\t" . $StockGLCodes['stockact'] . ",\n\t\t\t\t\t\t\t\t" . $myrow['standardcost'] * $StockQtyDifference . ", '" . $myrow['stockid'] . " x " . $StockQtyDifference . " @ " . $myrow['standardcost'] . " - " . _('Inventory Check') . "')";
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
}
//END INSERT GL TRANS
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('Unable to COMMIT transaction while adjusting stock in StockCheckAdjustmet report');
$SQL = "COMMIT";
$Result = DB_query($SQL, $db, $ErrMsg, '', true);
}
// end if $StockQtyDifference !=0
/*end if the transfer item is a controlled item */
$SQL = "UPDATE locstock\n\t\t\t\t\tSET quantity = quantity - '" . round($TrfLine->Quantity, $TrfLine->DecimalPlaces) . "'\n\t\t\t\t\tWHERE stockid='" . $TrfLine->StockID . "'\n\t\t\t\t\tAND loccode='" . $_SESSION['Transfer']->StockLocationFrom . "'";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The location stock record could not be updated because');
$DbgMsg = _('The following SQL to update the stock record was used');
$Result = DB_query($SQL, $ErrMsg, $DbgMsg, true);
$SQL = "UPDATE locstock\n\t\t\t\t\tSET quantity = quantity + '" . round($TrfLine->Quantity, $TrfLine->DecimalPlaces) . "'\n\t\t\t\t\tWHERE stockid='" . $TrfLine->StockID . "'\n\t\t\t\t\tAND loccode='" . $_SESSION['Transfer']->StockLocationTo . "'";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The location stock record could not be updated because');
$DbgMsg = _('The following SQL to update the stock record was used');
$Result = DB_query($SQL, $ErrMsg, $DbgMsg, true);
// Insert incoming inventory GL transaction if any of the locations has a GL account code:
if (($_SESSION['Transfer']->StockLocationFromAccount != '' or $_SESSION['Transfer']->StockLocationToAccount != '') and $TrfLine->Quantity != 0) {
// Get the account code:
if ($_SESSION['Transfer']->StockLocationToAccount != '') {
$AccountCode = $_SESSION['Transfer']->StockLocationToAccount;
} else {
$StockGLCode = GetStockGLCode($TrfLine->StockID, $db);
// Get Category's account codes.
$AccountCode = $StockGLCode['stockact'];
// Select account code for stock.
}
// Get the item cost:
$SQLstandardcost = "SELECT stockmaster.materialcost + stockmaster.labourcost + stockmaster.overheadcost AS standardcost\n\t\t\t\t\t\t\t\t\t\tFROM stockmaster\n\t\t\t\t\t\t\t\t\t\tWHERE stockmaster.stockid ='" . $TrfLine->StockID . "'";
$ErrMsg = _('The standard cost of the item cannot be retrieved because');
$DbgMsg = _('The SQL that failed was');
$myrow = DB_fetch_array(DB_query($SQLstandardcost, $ErrMsg, $DbgMsg));
$StandardCost = $myrow['standardcost'];
// QUESTION: Standard cost for: Assembly (value="A") and Manufactured (value="M") items ?
// Insert record:
$SQL = "INSERT INTO gltrans (\n\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\ttype,\n\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\tVALUES ('" . $PeriodNo . "','" . $SQLTransferDate . "',\n\t\t\t\t\t\t\t16,'" . $_SESSION['Transfer']->TrfID . "','" . $AccountCode . "','" . $_SESSION['Transfer']->StockLocationTo . ' - ' . $TrfLine->StockID . ' x ' . $TrfLine->Quantity . ' @ ' . $StandardCost . "','" . $TrfLine->Quantity * $StandardCost . "')";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The incoming inventory GL transacction record could not be inserted because');
$DbgMsg = _('The following SQL to insert records was used');
}
/* now insert the serial stock movement */
$SQL = "INSERT INTO stockserialmoves (stockmoveno,\r\n\t\t\t\t\t\t\t\t\tstockid,\r\n\t\t\t\t\t\t\t\t\tserialno,\r\n\t\t\t\t\t\t\t\t\tmoveqty)\r\n\t\t\t\t\t\tVALUES (" . $StkMoveNo . ",\r\n\t\t\t\t\t\t\t'" . $_SESSION['Adjustment']->StockID . "',\r\n\t\t\t\t\t\t\t'" . $Item->BundleRef . "',\r\n\t\t\t\t\t\t\t" . $Item->BundleQty . ")";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The serial stock movement record could not be inserted because');
$DbgMsg = _('The following SQL to insert the serial stock movement records was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
}
/* foreach controlled item in the serialitems array */
}
/*end if the adjustment item is a controlled item */
$SQL = "UPDATE locstock SET quantity = quantity - " . $_SESSION['Adjustment']->Quantity . "\r\n\t\t\t\tWHERE stockid='" . $_SESSION['Adjustment']->StockID . "'\r\n\t\t\t\tAND loccode='" . $_SESSION['Adjustment']->StockLocation . "'";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The location stock record could not be updated because');
$DbgMsg = _('The following SQL to update the stock record was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
if ($_SESSION['CompanyRecord']['gllink_stock'] == 1) {
$StockGLCodes = GetStockGLCode($_SESSION['Adjustment']->StockID, $db);
$SQL = "INSERT INTO gltrans (type,\r\n\t\t\t\t\t\t\ttypeno,\r\n\t\t\t\t\t\t\ttrandate,\r\n\t\t\t\t\t\t\tperiodno,\r\n\t\t\t\t\t\t\taccount,\r\n\t\t\t\t\t\t\tdefaulttag,\r\n\t\t\t\t\t\t\tamount,\r\n\t\t\t\t\t\t\tnarrative,\r\n\t\t\t\t\t\t\ttag)\r\n\t\t\t\t\tVALUES (14,\r\n\t\t\t\t\t\t" . $AdjustmentNumber . ",\r\n\t\t\t\t\t\t'" . FormatDateForSQL($_POST['issueddate']) . "',\r\n\t\t\t\t\t\t" . $PeriodNo . ",\r\n\t\t\t\t\t\t" . $StockGLCodes['adjglact'] . ",\r\n\t\t\t\t\t\t'" . $_SESSION['DefaultTag'] . "',\r\n\t\t\t\t\t\t" . $_SESSION['Adjustment']->StandardCost * $_SESSION['Adjustment']->Quantity . ",\r\n\t\t\t\t\t\t'" . $_SESSION['Adjustment']->StockID . " x " . $_SESSION['Adjustment']->Quantity . " @ " . $_SESSION['Adjustment']->StandardCost . " " . $_SESSION['Adjustment']->Narrative . "','" . $_POST['tag'] . "')";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The general ledger transaction entries could not be added because');
$DbgMsg = _('The following SQL to insert the GL entries was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
$SQL = "INSERT INTO gltrans (type,\r\n\t\t\t\t\t\t\ttypeno,\r\n\t\t\t\t\t\t\ttrandate,\r\n\t\t\t\t\t\t\tperiodno,\r\n\t\t\t\t\t\t\taccount,\r\n\t\t\t\t\t\t\tdefaulttag,\r\n\t\t\t\t\t\t\tamount,\r\n\t\t\t\t\t\t\tnarrative)\r\n\t\t\t\t\tVALUES (14,\r\n\t\t\t\t\t\t" . $AdjustmentNumber . ",\r\n\t\t\t\t\t\t'" . FormatDateForSQL($_POST['issueddate']) . "',\r\n\t\t\t\t\t\t" . $PeriodNo . ",\r\n\t\t\t\t\t\t" . $StockGLCodes['stockact'] . ",\r\n\t\t\t\t\t\t'" . $_SESSION['DefaultTag'] . "',\r\n\t\t\t\t\t\t" . $_SESSION['Adjustment']->StandardCost * -$_SESSION['Adjustment']->Quantity . ",\r\n\t\t\t\t\t\t'" . $_SESSION['Adjustment']->StockID . " x " . $_SESSION['Adjustment']->Quantity . " @ " . $_SESSION['Adjustment']->StandardCost . " " . $_SESSION['Adjustment']->Narrative . "')";
$Errmsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The general ledger transaction entries could not be added because');
$DbgMsg = _('The following SQL to insert the GL entries was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
}
$Result = DB_query('COMMIT', $db);
prnMsg(_('A stock issue for') . ' ' . $_SESSION['Adjustment']->StockID . ' - ' . $_SESSION['Adjustment']->ItemDescription . ' ' . _('has been created from location') . ' ' . $_SESSION['Adjustment']->StockLocation . ' ' . _('for a quantity of') . ' ' . $_SESSION['Adjustment']->Quantity, 'success');
echo '<br /><a href="PDFStockIssue.php?TransferNo=' . $AdjustmentNumber . '">Print Stock Issue Note</a>';
unset($_SESSION['Adjustment']);
}
/* end if there was no input error */
<th colspan="4" align="right"><b>' . _('Actual Other Costs') . '</b></th>
<th class="number"><b>' . locale_number_format($OtherReqtsActual, $_SESSION['CompanyRecord']['decimalplaces']) . '</b></th>
</tr>
</table></td>
</tr>
<tr>
<td colspan="5"><b>' . _('Total Budget Contract Cost') . '</b></td>
<td class="number"><b>' . locale_number_format($OtherReqtsBudget + $ContractBOMBudget, $_SESSION['CompanyRecord']['decimalplaces']) . '</b></td>
<td colspan="5"><b>' . _('Total Actual Contract Cost') . '</b></td>
<td class="number"><b>' . locale_number_format($OtherReqtsActual + $ContractBOMActual, $_SESSION['CompanyRecord']['decimalplaces']) . '</b></td>
</tr>
</table>';
//Do the processing here after the variances are all calculated above
if (isset($_POST['CloseContract']) and $_SESSION['Contract' . $identifier]->Status == 2) {
include 'includes/SQL_CommonFunctions.inc';
$GLCodes = GetStockGLCode($_SESSION['Contract' . $identifier]->ContractRef, $db);
//Compare actual costs to original budgeted contract costs - if actual > budgeted - CR WIP and DR usage variance
$Variance = $OtherReqtsBudget + $ContractBOMBudget - ($OtherReqtsActual + $ContractBOMActual);
$ContractCloseNo = GetNextTransNo(32, $db);
$PeriodNo = GetPeriod(Date($_SESSION['DefaultDateFormat']), $db);
DB_Txn_Begin($db);
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\tVALUES ( 32,\n\t\t\t\t\t\t\t'" . $ContractCloseNo . "',\n\t\t\t\t\t\t\t'" . Date('Y-m-d') . "',\n\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t'" . $GLCodes['wipact'] . "',\n\t\t\t\t\t\t\t'" . _('Variance on contract') . ' ' . $_SESSION['Contract' . $identifier]->ContractRef . "',\n\t\t\t\t\t\t\t'" . -$Variance . "')";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The gl entry of WIP for the variance on closing the contract could not be inserted because');
$DbgMsg = _('The following SQL to insert the GLTrans record was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\tVALUES ( 32,\n\t\t\t\t\t\t\t'" . $ContractCloseNo . "',\n\t\t\t\t\t\t\t'" . Date('Y-m-d') . "',\n\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t'" . $GLCodes['materialuseagevarac'] . "',\n\t\t\t\t\t\t\t'" . _('Variance on contract') . ' ' . $_SESSION['Contract' . $identifier]->ContractRef . "',\n\t\t\t\t\t\t\t'" . $Variance . "')";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The gl entry of WIP for the variance on closing the contract could not be inserted because');
$DbgMsg = _('The following SQL to insert the GLTrans record was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
//Now update the status of the contract to closed
$SQL = "UPDATE contracts\n\t\t\t\tSET status=3\n\t\t\t\tWHERE contractref='" . $_SESSION['Contract' . $identifier]->ContractRef . "'";
function InvoiceSalesOrder($OrderNo, $User, $Password)
{
$Errors = array();
$db = db($User, $Password);
if (gettype($db) == 'integer') {
$Errors[] = NoAuthorisation;
return $Errors;
}
$Errors = VerifyOrderHeaderExists($OrderNo, sizeof($Errors), $Errors, $db);
if (sizeof($Errors) != 0) {
return $Errors;
}
/*Does not deal with assembly items or serialise/lot track items - for use by POS */
/*Get Company Defaults */
$ReadCoyResult = api_DB_query("SELECT debtorsact,\n\t\t\t\t\t\t\t\t\t\t\t\tfreightact,\n\t\t\t\t\t\t\t\t\t\t\t\tgllink_debtors,\n\t\t\t\t\t\t\t\t\t\t\t\tgllink_stock\n\t\t\t\t\t\t\t\t\t\tFROM companies\n\t\t\t\t\t\t\t\t\t\tWHERE coycode=1", $db);
$CompanyRecord = DB_fetch_array($ReadCoyResult);
if (DB_error_no($db) != 0) {
$Errors[] = NoCompanyRecord;
}
$OrderHeaderSQL = "SELECT salesorders.debtorno,\n\t\t\t\t \t\t\t\t debtorsmaster.name,\n\t\t\t\t\t\t\t\t salesorders.branchcode,\n\t\t\t\t\t\t\t\t salesorders.customerref,\n\t\t\t\t\t\t\t\t salesorders.orddate,\n\t\t\t\t\t\t\t\t salesorders.ordertype,\n\t\t\t\t\t\t\t\t salesorders.shipvia,\n\t\t\t\t\t\t\t\t custbranch.area,\n\t\t\t\t\t\t\t\t custbranch.taxgroupid,\n\t\t\t\t\t\t\t\t debtorsmaster.currcode,\n\t\t\t\t\t\t\t\t currencies.rate,\n\t\t\t\t\t\t\t\t salesorders.fromstkloc,\n\t\t\t\t\t\t\t\t custbranch.salesman\n\t\t\t\t\t\t\tFROM salesorders\n\t\t\t\t\t\t\tINNER JOIN debtorsmaster\n\t\t\t\t\t\t\tON salesorders.debtorno = debtorsmaster.debtorno\n\t\t\t\t\t\t\tINNER JOIN custbranch\n\t\t\t\t\t\t\tON salesorders.debtorno = custbranch.debtorno\n\t\t\t\t\t\t\tAND salesorders.branchcode = custbranch.branchcode\n\t\t\t\t\t\t\tINNER JOIN locations\n\t\t\t\t\t\t\tON locations.loccode=salesorders.fromstkloc\n\t\t\t\t\t\t\tINNER JOIN currencies\n\t\t\t\t\t\t\tON debtorsmaster.currcode=currencies.currabrev\n\t\t\t\t\t\t\tWHERE salesorders.orderno = '" . $OrderNo . "'";
$OrderHeaderResult = api_DB_query($OrderHeaderSQL, $db);
if (DB_error_no($db) != 0) {
$Errors[] = NoReadOrder;
}
$OrderHeader = DB_fetch_array($OrderHeaderResult);
$TaxProvResult = api_DB_query("SELECT taxprovinceid FROM locations WHERE loccode='" . $OrderHeader['fromstkloc'] . "'", $db);
if (DB_error_no($db) != 0) {
$Errors[] = NoTaxProvince;
}
$myrow = DB_fetch_row($TaxProvResult);
$DispTaxProvinceID = $myrow[0];
$LineItemsSQL = "SELECT stkcode,\n\t\t\t\t\t\t\t\tunitprice,\n\t\t\t\t\t\t\t\tquantity,\n\t\t\t\t\t\t\t\tdiscountpercent,\n\t\t\t\t\t\t\t\ttaxcatid,\n\t\t\t\t\t\t\t\tmbflag,\n\t\t\t\t\t\t\t\tmaterialcost+labourcost+overheadcost AS standardcost\n\t\t\t\t\t\tFROM salesorderdetails INNER JOIN stockmaster\n\t\t\t\t\t\tON salesorderdetails.stkcode = stockmaster.stockid\n\t\t\t\t\t\tWHERE orderno ='" . $OrderNo . "'\n\t\t\t\t\t\tAND completed=0";
$LineItemsResult = api_DB_query($LineItemsSQL, $db);
if (DB_error_no($db) != 0 or DB_num_rows($LineItemsResult) == 0) {
$Errors[] = NoReadOrderLines;
return $Errors;
}
/*Start an SQL transaction */
$result = DB_Txn_Begin($db);
/*Now Get the next invoice number - function in SQL_CommonFunctions*/
$InvoiceNo = GetNextTransNo(10, $db);
$PeriodNo = GetCurrentPeriod($db);
$TaxTotals = array();
$TotalFXNetInvoice = 0;
$TotalFXTax = 0;
$LineCounter = 0;
while ($OrderLineRow = DB_fetch_array($LineItemsResult)) {
$StandardCost = $OrderLineRow['standardcost'];
$LocalCurrencyPrice = $OrderLineRow['unitprice'] * (1 - floatval($OrderLineRow['discountpercent'])) / $OrderHeader['rate'];
$LineNetAmount = $OrderLineRow['unitprice'] * $OrderLineRow['quantity'] * (1 - floatval($OrderLineRow['discountpercent']));
/*Gets the Taxes and rates applicable to this line from the TaxGroup of the branch and TaxCategory of the item
and the taxprovince of the dispatch location */
$SQL = "SELECT taxgrouptaxes.calculationorder,\n\t\t\t\t\t\t\ttaxauthorities.description,\n\t\t\t\t\t\t\ttaxgrouptaxes.taxauthid,\n\t\t\t\t\t\t\ttaxauthorities.taxglcode,\n\t\t\t\t\t\t\ttaxgrouptaxes.taxontax,\n\t\t\t\t\t\t\ttaxauthrates.taxrate\n\t\t\t\t\tFROM taxauthrates INNER JOIN taxgrouptaxes ON\n\t\t\t\t\t\ttaxauthrates.taxauthority=taxgrouptaxes.taxauthid\n\t\t\t\t\t\tINNER JOIN taxauthorities ON\n\t\t\t\t\t\ttaxauthrates.taxauthority=taxauthorities.taxid\n\t\t\t\t\tWHERE taxgrouptaxes.taxgroupid='" . $OrderHeader['taxgroupid'] . "'\n\t\t\t\t\tAND taxauthrates.dispatchtaxprovince='" . $DispTaxProvinceID . "'\n\t\t\t\t\tAND taxauthrates.taxcatid = '" . $OrderLineRow['taxcatid'] . "'\n\t\t\t\t\tORDER BY taxgrouptaxes.calculationorder";
$GetTaxRatesResult = api_DB_query($SQL, $db);
if (DB_error_no($db) != 0) {
$Errors[] = TaxRatesFailed;
}
$LineTaxAmount = 0;
while ($myrow = DB_fetch_array($GetTaxRatesResult)) {
if (!isset($TaxTotals[$myrow['taxauthid']]['FXAmount'])) {
$TaxTotals[$myrow['taxauthid']]['FXAmount'] = 0;
}
$TaxAuthID = $myrow['taxauthid'];
$TaxTotals[$myrow['taxauthid']]['GLCode'] = $myrow['taxglcode'];
$TaxTotals[$myrow['taxauthid']]['TaxRate'] = $myrow['taxrate'];
$TaxTotals[$myrow['taxauthid']]['TaxAuthDescription'] = $myrow['description'];
if ($myrow['taxontax'] == 1) {
$TaxAuthAmount = ($LineNetAmount + $LineTaxAmount) * $myrow['taxrate'];
} else {
$TaxAuthAmount = $LineNetAmount * $myrow['taxrate'];
}
$TaxTotals[$myrow['taxauthid']]['FXAmount'] += $TaxAuthAmount;
/*Make an array of the taxes and amounts including GLcodes for later posting - need debtortransid
so can only post once the debtor trans is posted - can only post debtor trans when all tax is calculated */
$LineTaxes[$LineCounter][$myrow['calculationorder']] = array('TaxCalculationOrder' => $myrow['calculationorder'], 'TaxAuthID' => $myrow['taxauthid'], 'TaxAuthDescription' => $myrow['description'], 'TaxRate' => $myrow['taxrate'], 'TaxOnTax' => $myrow['taxontax'], 'TaxAuthAmount' => $TaxAuthAmount);
$LineTaxAmount += $TaxAuthAmount;
}
//end loop around Taxes
$TotalFXNetInvoice += $LineNetAmount;
$TotalFXTax += $LineTaxAmount;
/*Now update SalesOrderDetails for the quantity invoiced and the actual dispatch dates. */
$SQL = "UPDATE salesorderdetails\n\t\t\t\t\tSET qtyinvoiced = qtyinvoiced + " . $OrderLineRow['quantity'] . ",\n\t\t\t\t\t\tactualdispatchdate = '" . $OrderHeader['orddate'] . "',\n\t\t\t\t\t\tcompleted='1'\n\t\t\t\t\tWHERE orderno = '" . $OrderNo . "'\n\t\t\t\t\tAND stkcode = '" . $OrderLineRow['stkcode'] . "'";
$Result = api_DB_query($SQL, $db, '', '', true);
if ($OrderLineRow['mbflag'] == 'B' or $OrderLineRow['mbflag'] == 'M') {
$Assembly = False;
/* Need to get the current location quantity
will need it later for the stock movement */
$SQL = "SELECT locstock.quantity\n\t\t\t\t\t\tFROM locstock\n\t\t\t\t\t\tWHERE locstock.stockid='" . $OrderLineRow['stkcode'] . "'\n\t\t\t\t\t\tAND loccode= '" . $OrderHeader['fromstkloc'] . "'";
$Result = api_DB_query($SQL, $db);
if (DB_num_rows($Result) == 1) {
$LocQtyRow = DB_fetch_row($Result);
$QtyOnHandPrior = $LocQtyRow[0];
} else {
/* There must be some error this should never happen */
$QtyOnHandPrior = 0;
}
$SQL = "UPDATE locstock\n\t\t\t\t\t\tSET quantity = locstock.quantity - " . $OrderLineRow['quantity'] . "\n\t\t\t\t\t\tWHERE locstock.stockid = '" . $OrderLineRow['stkcode'] . "'\n\t\t\t\t\t\tAND loccode = '" . $OrderHeader['fromstkloc'] . "'";
$Result = api_DB_query($SQL, $db, '', '', true);
$SQL = "INSERT INTO stockmoves (stockid,\n\t\t\t\t\t\t\t\t\t\t\t\ttype,\n\t\t\t\t\t\t\t\t\t\t\t\ttransno,\n\t\t\t\t\t\t\t\t\t\t\t\tloccode,\n\t\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\t\tdebtorno,\n\t\t\t\t\t\t\t\t\t\t\t\tbranchcode,\n\t\t\t\t\t\t\t\t\t\t\t\tprice,\n\t\t\t\t\t\t\t\t\t\t\t\tprd,\n\t\t\t\t\t\t\t\t\t\t\t\treference,\n\t\t\t\t\t\t\t\t\t\t\t\tqty,\n\t\t\t\t\t\t\t\t\t\t\t\tdiscountpercent,\n\t\t\t\t\t\t\t\t\t\t\t\tstandardcost,\n\t\t\t\t\t\t\t\t\t\t\t\tnewqoh)\n\t\t\t\t\t\tVALUES ('" . $OrderLineRow['stkcode'] . "',\n\t\t\t\t\t\t\t\t'10',\n\t\t\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t\t\t'" . $OrderHeader['fromstkloc'] . "',\n\t\t\t\t\t\t\t\t'" . $OrderHeader['orddate'] . "',\n\t\t\t\t\t\t\t\t'" . $OrderHeader['debtorno'] . "',\n\t\t\t\t\t\t\t\t'" . $OrderHeader['branchcode'] . "',\n\t\t\t\t\t\t\t\t'" . $LocalCurrencyPrice . "',\n\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t'" . $OrderNo . "',\n\t\t\t\t\t\t\t\t'" . -$OrderLineRow['quantity'] . "',\n\t\t\t\t\t\t\t\t'" . $OrderLineRow['discountpercent'] . "',\n\t\t\t\t\t\t\t\t'" . $StandardCost . "',\n\t\t\t\t\t\t\t\t'" . ($QtyOnHandPrior - $OrderLineRow['quantity']) . "' )";
$Result = api_DB_query($SQL, $db, '', '', true);
} else {
if ($OrderLineRow['mbflag'] == 'A') {
/* its an assembly */
/*Need to get the BOM for this part and make
stock moves for the components then update the Location stock balances */
$Assembly = True;
$StandardCost = 0;
/*To start with - accumulate the cost of the comoponents for use in journals later on */
$SQL = "SELECT bom.component,\n\t\t\t\t\t\t\t\tbom.quantity,\n\t\t\t\t\t\t\t\tstockmaster.materialcost+stockmaster.labourcost+stockmaster.overheadcost AS standard\n\t\t\t\t\t\t\tFROM bom INNER JOIN stockmaster\n\t\t\t\t\t\t\tON bom.component=stockmaster.stockid\n\t\t\t\t\t\t\tWHERE bom.parent='" . $OrderLineRow['stkcode'] . "'\n\t\t\t\t\t\t\tAND bom.effectiveto >= '" . Date('Y-m-d') . "'\n\t\t\t\t\t\t\tAND bom.effectiveafter < '" . Date('Y-m-d') . "'";
$AssResult = api_DB_query($SQL, $db);
while ($AssParts = DB_fetch_array($AssResult, $db)) {
$StandardCost += $AssParts['standard'] * $AssParts['quantity'];
/* Need to get the current location quantity
will need it later for the stock movement */
$SQL = "SELECT locstock.quantity\n\t\t\t\t\t\t\tFROM locstock\n\t\t\t\t\t\t\tWHERE locstock.stockid='" . $AssParts['component'] . "'\n\t\t\t\t\t\t\tAND loccode= '" . $OrderHeader['fromstkloc'] . "'";
$Result = api_DB_query($SQL, $db);
if (DB_num_rows($Result) == 1) {
$LocQtyRow = DB_fetch_row($Result);
$QtyOnHandPrior = $LocQtyRow[0];
} else {
/*There must be some error this should never happen */
$QtyOnHandPrior = 0;
}
if (empty($AssParts['standard'])) {
$AssParts['standard'] = 0;
}
$SQL = "INSERT INTO stockmoves (stockid,\n\t\t\t\t\t\t\t\t\t\t\t\t\ttype,\n\t\t\t\t\t\t\t\t\t\t\t\t\ttransno,\n\t\t\t\t\t\t\t\t\t\t\t\t\tloccode,\n\t\t\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\t\t\tdebtorno,\n\t\t\t\t\t\t\t\t\t\t\t\t\tbranchcode,\n\t\t\t\t\t\t\t\t\t\t\t\t\tprd,\n\t\t\t\t\t\t\t\t\t\t\t\t\treference,\n\t\t\t\t\t\t\t\t\t\t\t\t\tqty,\n\t\t\t\t\t\t\t\t\t\t\t\t\tstandardcost,\n\t\t\t\t\t\t\t\t\t\t\t\t\tshow_on_inv_crds,\n\t\t\t\t\t\t\t\t\t\t\t\t\tnewqoh)\n\t\t\t\t\t\t\t\t\t\tVALUES ('" . $AssParts['component'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t 10,\n\t\t\t\t\t\t\t\t\t\t\t\t '" . $InvoiceNo . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . $OrderHeader['fromstkloc'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . $DefaultDispatchDate . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . $OrderHeader['debtorno'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . $OrderHeader['branchcode'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . _('Assembly') . ': ' . $OrderLineRow['stkcode'] . ' ' . _('Order') . ': ' . $OrderNo . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . -$AssParts['quantity'] * $OrderLineRow['quantity'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . $AssParts['standard'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t 0,\n\t\t\t\t\t\t\t\t\t\t\t\t '" . ($QtyOnHandPrior - $AssParts['quantity'] * $OrderLineRow['quantity']) . "'\t)";
$Result = DB_query($SQL, $db, '', '', true);
$SQL = "UPDATE locstock\n\t\t\t\t\t\t\tSET quantity = locstock.quantity - " . $AssParts['quantity'] * $OrderLineRow['quantity'] . "\n\t\t\t\t\t\t\tWHERE locstock.stockid = '" . $AssParts['component'] . "'\n\t\t\t\t\t\t\tAND loccode = '" . $OrderHeader['fromlocstk'] . "'";
$Result = DB_query($SQL, $db, '', '', true);
}
/* end of assembly explosion and updates */
}
}
/* end of its an assembly */
if ($OrderLineRow['mbflag'] == 'A' or $OrderLineRow['mbflag'] == 'D') {
/*it's a Dummy/Service item or an Assembly item - still need stock movement record
* but quantites on hand are always nil */
$SQL = "INSERT INTO stockmoves (stockid,\n\t\t\t\t\t\t\t\t\t\t\t\ttype,\n\t\t\t\t\t\t\t\t\t\t\t\ttransno,\n\t\t\t\t\t\t\t\t\t\t\t\tloccode,\n\t\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\t\tdebtorno,\n\t\t\t\t\t\t\t\t\t\t\t\tbranchcode,\n\t\t\t\t\t\t\t\t\t\t\t\tprice,\n\t\t\t\t\t\t\t\t\t\t\t\tprd,\n\t\t\t\t\t\t\t\t\t\t\t\treference,\n\t\t\t\t\t\t\t\t\t\t\t\tqty,\n\t\t\t\t\t\t\t\t\t\t\t\tdiscountpercent,\n\t\t\t\t\t\t\t\t\t\t\t\tstandardcost,\n\t\t\t\t\t\t\t\t\t\t\t\tnewqoh)\n\t\t\t\t\t\tVALUES ('" . $OrderLineRow['stkcode'] . "',\n\t\t\t\t\t\t\t\t'10',\n\t\t\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t\t\t'" . $OrderHeader['fromstkloc'] . "',\n\t\t\t\t\t\t\t\t'" . $OrderHeader['orddate'] . "',\n\t\t\t\t\t\t\t\t'" . $OrderHeader['debtorno'] . "',\n\t\t\t\t\t\t\t\t'" . $OrderHeader['branchcode'] . "',\n\t\t\t\t\t\t\t\t'" . $LocalCurrencyPrice . "',\n\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t'" . $OrderNo . "',\n\t\t\t\t\t\t\t\t'" . -$OrderLineRow['quantity'] . "',\n\t\t\t\t\t\t\t\t'" . $OrderLineRow['discountpercent'] . "',\n\t\t\t\t\t\t\t\t'" . $StandardCost . "',\n\t\t\t\t\t\t\t\t'0' )";
$Result = api_DB_query($SQL, $db, '', '', true);
}
/*Get the ID of the StockMove... */
$StkMoveNo = DB_Last_Insert_ID($db, 'stockmoves', 'stkmoveno');
/*Insert the taxes that applied to this line */
foreach ($LineTaxes[$LineCounter] as $Tax) {
$SQL = "INSERT INTO stockmovestaxes (stkmoveno,\n\t\t\t\t\t\t\t\t\ttaxauthid,\n\t\t\t\t\t\t\t\t\ttaxrate,\n\t\t\t\t\t\t\t\t\ttaxcalculationorder,\n\t\t\t\t\t\t\t\t\ttaxontax)\n\t\t\t\t\t\tVALUES ('" . $StkMoveNo . "',\n\t\t\t\t\t\t\t'" . $Tax['TaxAuthID'] . "',\n\t\t\t\t\t\t\t'" . $Tax['TaxRate'] . "',\n\t\t\t\t\t\t\t'" . $Tax['TaxCalculationOrder'] . "',\n\t\t\t\t\t\t\t'" . $Tax['TaxOnTax'] . "')";
$Result = DB_query($SQL, $db, '', '', true);
}
/*Insert Sales Analysis records */
$SQL = "SELECT COUNT(*),\n\t\t\t\t\t\tsalesanalysis.stkcategory,\n\t\t\t\t\t\tsalesanalysis.area,\n\t\t\t\t\t\tsalesanalysis.salesperson,\n\t\t\t\t\t\tsalesanalysis.periodno,\n\t\t\t\t\t\tsalesanalysis.typeabbrev,\n\t\t\t\t\t\tsalesanalysis.cust,\n\t\t\t\t\t\tsalesanalysis.custbranch,\n\t\t\t\t\t\tsalesanalysis.stockid\n\t\t\t\t\tFROM salesanalysis,\n\t\t\t\t\t\tcustbranch,\n\t\t\t\t\t\tstockmaster\n\t\t\t\t\tWHERE salesanalysis.stkcategory=stockmaster.categoryid\n\t\t\t\t\tAND salesanalysis.stockid=stockmaster.stockid\n\t\t\t\t\tAND salesanalysis.cust=custbranch.debtorno\n\t\t\t\t\tAND salesanalysis.custbranch=custbranch.branchcode\n\t\t\t\t\tAND salesanalysis.area=custbranch.area\n\t\t\t\t\tAND salesanalysis.salesperson=custbranch.salesman\n\t\t\t\t\tAND salesanalysis.typeabbrev ='" . $OrderHeader['ordertype'] . "'\n\t\t\t\t\tAND salesanalysis.periodno='" . $PeriodNo . "'\n\t\t\t\t\tAND salesanalysis.cust " . LIKE . " '" . $OrderHeader['debtorno'] . "'\n\t\t\t\t\tAND salesanalysis.custbranch " . LIKE . " '" . $OrderHeader['branchcode'] . "'\n\t\t\t\t\tAND salesanalysis.stockid " . LIKE . " '" . $OrderLineRow['stkcode'] . "'\n\t\t\t\t\tAND salesanalysis.budgetoractual='1'\n\t\t\t\t\tGROUP BY salesanalysis.stockid,\n\t\t\t\t\t\tsalesanalysis.stkcategory,\n\t\t\t\t\t\tsalesanalysis.cust,\n\t\t\t\t\t\tsalesanalysis.custbranch,\n\t\t\t\t\t\tsalesanalysis.area,\n\t\t\t\t\t\tsalesanalysis.periodno,\n\t\t\t\t\t\tsalesanalysis.typeabbrev,\n\t\t\t\t\t\tsalesanalysis.salesperson";
$ErrMsg = _('The count of existing Sales analysis records could not run because');
$DbgMsg = _('SQL to count the no of sales analysis records');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
$myrow = DB_fetch_row($Result);
if ($myrow[0] > 0) {
/*Update the existing record that already exists */
$SQL = "UPDATE salesanalysis\n\t\t\t\t\t\tSET amt=amt+" . filter_number_format($OrderLineRow['unitprice'] * $OrderLineRow['quantity'] / $OrderHeader['rate']) . ",\n\t\t\t\t\t\tqty=qty +" . $OrderLineRow['quantity'] . ",\n\t\t\t\t\t\tdisc=disc+" . filter_number_format($OrderLineRow['discountpercent'] * $OrderLineRow['unitprice'] * $OrderLineRow['quantity'] / $OrderHeader['rate']) . "\n\t\t\t\t\t\tWHERE salesanalysis.area='" . $myrow[2] . "'\n\t\t\t\t\t\tAND salesanalysis.salesperson='" . $myrow[3] . "'\n\t\t\t\t\t\tAND typeabbrev ='" . $OrderHeader['ordertype'] . "'\n\t\t\t\t\t\tAND periodno = '" . $PeriodNo . "'\n\t\t\t\t\t\tAND cust " . LIKE . " '" . $OrderHeader['debtorno'] . "'\n\t\t\t\t\t\tAND custbranch " . LIKE . " '" . $OrderHeader['branchcode'] . "'\n\t\t\t\t\t\tAND stockid " . LIKE . " '" . $OrderLineRow['stkcode'] . "'\n\t\t\t\t\t\tAND salesanalysis.stkcategory ='" . $myrow[1] . "'\n\t\t\t\t\t\tAND budgetoractual='1'";
} else {
/* insert a new sales analysis record */
$SQL = "INSERT INTO salesanalysis (\ttypeabbrev,\n\t\t\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\t\t\tamt,\n\t\t\t\t\t\t\t\t\t\t\t\t\tcost,\n\t\t\t\t\t\t\t\t\t\t\t\t\tcust,\n\t\t\t\t\t\t\t\t\t\t\t\t\tcustbranch,\n\t\t\t\t\t\t\t\t\t\t\t\t\tqty,\n\t\t\t\t\t\t\t\t\t\t\t\t\tdisc,\n\t\t\t\t\t\t\t\t\t\t\t\t\tstockid,\n\t\t\t\t\t\t\t\t\t\t\t\t\tarea,\n\t\t\t\t\t\t\t\t\t\t\t\t\tbudgetoractual,\n\t\t\t\t\t\t\t\t\t\t\t\t\tsalesperson,\n\t\t\t\t\t\t\t\t\t\t\t\t\tstkcategory )\n\t\t\t\t\t\t\t\tSELECT '" . $OrderHeader['ordertype'] . "',\n\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t'" . $OrderLineRow['unitprice'] * $OrderLineRow['quantity'] / $OrderHeader['rate'] . "',\n\t\t\t\t\t\t\t\t\t0,\n\t\t\t\t\t\t\t\t\t'" . $OrderHeader['debtorno'] . "',\n\t\t\t\t\t\t\t\t\t'" . $OrderHeader['branchcode'] . "',\n\t\t\t\t\t\t\t\t\t'" . $OrderLineRow['quantity'] . "',\n\t\t\t\t\t\t\t\t\t'" . $OrderLineRow['discountpercent'] * $OrderLineRow['unitprice'] * $OrderLineRow['quantity'] / $OrderHeader['rate'] . "',\n\t\t\t\t\t\t\t\t\t'" . $OrderLineRow['stkcode'] . "',\n\t\t\t\t\t\t\t\t\tcustbranch.area,\n\t\t\t\t\t\t\t\t\t1,\n\t\t\t\t\t\t\t\t\tcustbranch.salesman,\n\t\t\t\t\t\t\t\t\tstockmaster.categoryid\n\t\t\t\t\t\t\t\tFROM stockmaster, custbranch\n\t\t\t\t\t\t\t\tWHERE stockmaster.stockid = '" . $OrderLineRow['stkcode'] . "'\n\t\t\t\t\t\t\t\tAND custbranch.debtorno = '" . $OrderHeader['debtorno'] . "'\n\t\t\t\t\t\t\t\tAND custbranch.branchcode='" . $OrderHeader['branchcode'] . "'";
}
$Result = api_DB_query($SQL, $db, '', '', true);
if ($CompanyRecord['gllink_stock'] == 1 and $StandardCost != 0) {
/*first the cost of sales entry - GL accounts are retrieved using the function GetCOGSGLAccount from includes/GetSalesTransGLCodes.inc */
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\t\t\t\tVALUES (10,\n\t\t\t\t\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['orddate'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . GetCOGSGLAccount($OrderHeader['area'], $OrderLineRow['stkcode'], $OrderHeader['ordertype'], $db) . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['debtorno'] . " - " . $OrderLineRow['stkcode'] . " x " . $OrderLineRow['quantity'] . " @ " . $StandardCost . "',\n\t\t\t\t\t\t\t\t\t\t'" . $StandardCost * $OrderLineRow['quantity'] . "')";
$Result = api_DB_query($SQL, $db, '', '', true);
/*now the stock entry - this is set to the cost act in the case of a fixed asset disposal */
$StockGLCode = GetStockGLCode($OrderLineRow['stkcode'], $db);
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\t\t\t\tVALUES (10,\n\t\t\t\t\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['orddate'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $StockGLCode['stockact'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['debtorno'] . " - " . $OrderLineRow['stkcode'] . " x " . $OrderLineRow['quantity'] . " @ " . $StandardCost . "',\n\t\t\t\t\t\t\t\t\t\t'" . -$StandardCost * $OrderLineRow['quantity'] . "')";
$Result = api_DB_query($SQL, $db, '', '', true);
}
/* end of if GL and stock integrated and standard cost !=0 and not an asset */
if ($CompanyRecord['gllink_debtors'] == 1 and $OrderLineRow['unitprice'] != 0) {
//Post sales transaction to GL credit sales
$SalesGLAccounts = GetSalesGLAccount($OrderHeader['area'], $OrderLineRow['stkcode'], $OrderHeader['ordertype'], $db);
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\tamount )\n\t\t\t\t\tVALUES ('10',\n\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t'" . $OrderHeader['orddate'] . "',\n\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t'" . $SalesGLAccounts['salesglcode'] . "',\n\t\t\t\t\t\t'" . $OrderHeader['debtorno'] . " - " . $OrderLineRow['stkcode'] . " x " . $OrderLineRow['quantity'] . " @ " . $OrderLineRow['unitprice'] . "',\n\t\t\t\t\t\t'" . -$OrderLineRow['unitprice'] * $OrderLineRow['quantity'] / $OrderHeader['rate'] . "'\n\t\t\t\t\t)";
$Result = api_DB_query($SQL, $db, '', '', true);
if ($OrderLineRow['discountpercent'] != 0) {
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\t\tVALUES (10,\n\t\t\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t\t\t'" . $OrderHeader['orddate'] . "',\n\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t'" . $SalesGLAccounts['discountglcode'] . "',\n\t\t\t\t\t\t\t\t'" . $OrderHeader['debtorno'] . " - " . $OrderLineRow['stkcode'] . " @ " . $OrderLineRow['discountpercent'] * 100 . "%',\n\t\t\t\t\t\t\t\t'" . $OrderLineRow['unitprice'] * $OrderLineRow['quantity'] * $OrderLineRow['discountpercent'] / $OrderHeader['rate'] . "')";
$Result = DB_query($SQL, $db, '', '', true);
}
/*end of if discount !=0 */
}
/*end of if sales integrated with gl */
$LineCounter++;
//needed for the array of taxes by line
}
/*end of OrderLine loop */
$TotalInvLocalCurr = ($TotalFXNetInvoice + $TotalFXTax) / $OrderHeader['rate'];
if ($CompanyRecord['gllink_debtors'] == 1) {
/*Now post the tax to the GL at local currency equivalent */
if ($CompanyRecord['gllink_debtors'] == 1 and $TaxAuthAmount != 0) {
/*Loop through the tax authorities array to post each total to the taxauth glcode */
foreach ($TaxTotals as $Tax) {
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\t\tamount )\n\t\t\t\t\t\t\t\t\t\t\tVALUES (10,\n\t\t\t\t\t\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['orddate'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $Tax['GLCode'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['debtorno'] . "-" . $Tax['TaxAuthDescription'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . -$Tax['FXAmount'] / $OrderHeader['rate'] . "' )";
$Result = api_DB_query($SQL, $db, '', '', true);
}
}
/*Post debtors transaction to GL debit debtors, credit freight re-charged and credit sales */
if ($TotalInvLocalCurr != 0) {
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\t\t\t\tVALUES ('10',\n\t\t\t\t\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['orddate'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $CompanyRecord['debtorsact'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['debtorno'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $TotalInvLocalCurr . "')";
$Result = api_DB_query($SQL, $db, '', '', true);
}
EnsureGLEntriesBalance(10, $InvoiceNo, $db);
}
/*end of if Sales and GL integrated */
/*Update order header for invoice charged on */
$SQL = "UPDATE salesorders SET comments = CONCAT(comments,' Inv ','" . $InvoiceNo . "') WHERE orderno= '" . $OrderNo . "'";
$Result = api_DB_query($SQL, $db, '', '', true);
/*Now insert the DebtorTrans */
$SQL = "INSERT INTO debtortrans (transno,\n\t\t\t\t\t\t\t\t\t\ttype,\n\t\t\t\t\t\t\t\t\t\tdebtorno,\n\t\t\t\t\t\t\t\t\t\tbranchcode,\n\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\tinputdate,\n\t\t\t\t\t\t\t\t\t\tprd,\n\t\t\t\t\t\t\t\t\t\treference,\n\t\t\t\t\t\t\t\t\t\ttpe,\n\t\t\t\t\t\t\t\t\t\torder_,\n\t\t\t\t\t\t\t\t\t\tovamount,\n\t\t\t\t\t\t\t\t\t\tovgst,\n\t\t\t\t\t\t\t\t\t\trate,\n\t\t\t\t\t\t\t\t\t\tshipvia,\n\t\t\t\t\t\t\t\t\t\tsalesperson)\n\t\t\t\t\t\t\t\t\tVALUES (\n\t\t\t\t\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t\t\t\t\t10,\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['debtorno'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['branchcode'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['orddate'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . date('Y-m-d H-i-s') . "',\n\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['customerref'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['ordertype'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $TotalFXNetInvoice . "',\n\t\t\t\t\t\t\t\t\t\t'" . $TotalFXTax . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['rate'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['shipvia'] . "',\n+\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['salesman'] . "')";
$Result = api_DB_query($SQL, $db, '', '', true);
$DebtorTransID = DB_Last_Insert_ID($db, 'debtortrans', 'id');
/*for each Tax - need to insert into debtortranstaxes */
foreach ($TaxTotals as $TaxAuthID => $Tax) {
$SQL = "INSERT INTO debtortranstaxes (debtortransid,\n\t\t\t\t\t\t\t\t\t\t\t\ttaxauthid,\n\t\t\t\t\t\t\t\t\t\t\t\ttaxamount)\n\t\t\t\t\t\t\t\tVALUES ('" . $DebtorTransID . "',\n\t\t\t\t\t\t\t\t\t\t'" . $TaxAuthID . "',\n\t\t\t\t\t\t\t\t\t\t'" . $Tax['FXAmount'] / $OrderHeader['rate'] . "')";
$Result = api_DB_query($SQL, $db, '', '', true);
}
if (sizeof($Errors) == 0) {
$Result = DB_Txn_Commit($db);
$Errors[0] = 0;
$Errors[1] = $InvoiceNo;
} else {
$Result = DB_Txn_Rollback($db);
}
return $Errors;
}
$SQL = "SELECT quantity FROM locstock\n\t\t\t\tWHERE stockid ='" . $_POST['IssueItem'] . "'\n\t\t\t\tAND loccode ='" . $_POST['FromLocation'] . "'";
$CheckNegResult = DB_query($SQL, $db);
$CheckNegRow = DB_fetch_array($CheckNegResult);
if ($CheckNegRow['quantity'] < $QuantityIssued) {
$InputError = true;
prnMsg(_('This issue cannot be processed because the system parameter is set to prohibit negative stock and this issue would result in stock going into negative. Please correct the stock first before attempting another issue'), 'error');
}
}
if ($InputError == false) {
/************************ BEGIN SQL TRANSACTIONS ************************/
$Result = DB_Txn_Begin($db);
/*Now Get the next WO Issue transaction type 28 - function in SQL_CommonFunctions*/
$WOIssueNo = GetNextTransNo(28, $db);
$PeriodNo = GetPeriod(Date($_SESSION['DefaultDateFormat']), $db);
$SQLIssuedDate = FormatDateForSQL($_POST['IssuedDate']);
$StockGLCode = GetStockGLCode($_POST['IssueItem'], $db);
if ($IssueItemRow['mbflag'] == 'M' or $IssueItemRow['mbflag'] == 'B') {
/* Need to get the current location quantity will need it later for the stock movement */
$SQL = "SELECT locstock.quantity\n\t\t\t\tFROM locstock\n\t\t\t\tWHERE locstock.stockid='" . $_POST['IssueItem'] . "'\n\t\t\t\tAND loccode= '" . $_POST['FromLocation'] . "'";
$Result = DB_query($SQL, $db);
if (DB_num_rows($Result) == 1) {
$LocQtyRow = DB_fetch_array($Result);
$NewQtyOnHand = $LocQtyRow['quantity'] - $QuantityIssued;
} else {
/*There must actually be some error this should never happen */
$NewQtyOnHand = 0;
}
$SQL = "UPDATE locstock\n\t\t\t\tSET quantity = locstock.quantity - " . $QuantityIssued . "\n\t\t\t\tWHERE locstock.stockid = '" . $_POST['IssueItem'] . "'\n\t\t\t\tAND loccode = '" . $_POST['FromLocation'] . "'";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The location stock record could not be updated because');
$DbgMsg = _('The following SQL to update the location stock record was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
} else {
/* insert a new sales analysis record */
$SQL = "INSERT INTO salesanalysis (\ttypeabbrev,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tperiodno,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tamt,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tcost,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tcust,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tcustbranch,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tqty,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tdisc,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tstockid,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tarea,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tbudgetoractual,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tsalesperson,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tstkcategory\t)\r\n\t\t\t\t\tSELECT '" . $_SESSION['Items' . $identifier]->DefaultSalesType . "',\r\n\t\t\t\t\t\t'" . $PeriodNo . "',\r\n\t\t\t\t\t\t'" . -($ReturnItemLine->Price * $ReturnItemLine->Quantity / $ExRate) . "',\r\n\t\t\t\t\t\t'" . -($ReturnItemLine->StandardCost * $ReturnItemLine->Quantity) . "',\r\n\t\t\t\t\t\t'" . $_SESSION['Items' . $identifier]->DebtorNo . "',\r\n\t\t\t\t\t\t'" . $_SESSION['Items' . $identifier]->Branch . "',\r\n\t\t\t\t\t\t'" . -$ReturnItemLine->Quantity . "',\r\n\t\t\t\t\t\t'" . -($ReturnItemLine->DiscountPercent * $ReturnItemLine->Price * $ReturnItemLine->Quantity / $ExRate) . "',\r\n\t\t\t\t\t\t'" . $ReturnItemLine->StockID . "',\r\n\t\t\t\t\t\tcustbranch.area,\r\n\t\t\t\t\t\t1,\r\n\t\t\t\t\t\t'" . $_SESSION['Items' . $identifier]->SalesPerson . "',\r\n\t\t\t\t\t\tstockmaster.categoryid\r\n\t\t\t\t\tFROM stockmaster,\r\n\t\t\t\t\t\tcustbranch\r\n\t\t\t\t\tWHERE stockmaster.stockid = '" . $ReturnItemLine->StockID . "'\r\n\t\t\t\t\tAND custbranch.debtorno = '" . $_SESSION['Items' . $identifier]->DebtorNo . "'\r\n\t\t\t\t\tAND custbranch.branchcode='" . $_SESSION['Items' . $identifier]->Branch . "'";
}
$ErrMsg = _('Sales analysis record could not be added or updated because');
$DbgMsg = _('The following SQL to insert the sales analysis record was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
/* If GLLink_Stock then insert GLTrans to credit stock and debit cost of sales at standard cost*/
if ($_SESSION['CompanyRecord']['gllink_stock'] == 1 and $ReturnItemLine->StandardCost != 0) {
/*first the cost of sales entry*/
$SQL = "INSERT INTO gltrans (\ttype,\r\n\t\t\t\t\t\t\t\t\t\t\t\ttypeno,\r\n\t\t\t\t\t\t\t\t\t\t\t\ttrandate,\r\n\t\t\t\t\t\t\t\t\t\t\t\tperiodno,\r\n\t\t\t\t\t\t\t\t\t\t\t\taccount,\r\n\t\t\t\t\t\t\t\t\t\t\t\tnarrative,\r\n\t\t\t\t\t\t\t\t\t\t\t\tamount)\r\n\t\t\t\t\t\t\t\t\t\tVALUES ( 11,\r\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $CreditNoteNo . "',\r\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $ReturnDate . "',\r\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\r\n\t\t\t\t\t\t\t\t\t\t\t\t'" . GetCOGSGLAccount($Area, $ReturnItemLine->StockID, $_SESSION['Items' . $identifier]->DefaultSalesType, $db) . "',\r\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['Items' . $identifier]->DebtorNo . " - " . $ReturnItemLine->StockID . " x " . -$ReturnItemLine->Quantity . " @ " . $ReturnItemLine->StandardCost . "',\r\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $ReturnItemLine->StandardCost * -$ReturnItemLine->Quantity . "')";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The cost of sales GL posting could not be inserted because');
$DbgMsg = _('The following SQL to insert the GLTrans record was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
/*now the stock entry*/
$StockGLCode = GetStockGLCode($ReturnItemLine->StockID, $db);
$SQL = "INSERT INTO gltrans (type,\r\n\t\t\t\t\t\t\t\t\t\t\ttypeno,\r\n\t\t\t\t\t\t\t\t\t\t\ttrandate,\r\n\t\t\t\t\t\t\t\t\t\t\tperiodno,\r\n\t\t\t\t\t\t\t\t\t\t\taccount,\r\n\t\t\t\t\t\t\t\t\t\t\tnarrative,\r\n\t\t\t\t\t\t\t\t\t\t\tamount )\r\n\t\t\t\t\t\t\t\t\t\tVALUES ( 11,\r\n\t\t\t\t\t\t\t\t\t\t\t'" . $CreditNoteNo . "',\r\n\t\t\t\t\t\t\t\t\t\t\t'" . $ReturnDate . "',\r\n\t\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\r\n\t\t\t\t\t\t\t\t\t\t\t'" . $StockGLCode['stockact'] . "',\r\n\t\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['Items' . $identifier]->DebtorNo . " - " . $ReturnItemLine->StockID . " x " . -$ReturnItemLine->Quantity . " @ " . $ReturnItemLine->StandardCost . "',\r\n\t\t\t\t\t\t\t\t\t\t\t'" . $ReturnItemLine->StandardCost * $ReturnItemLine->Quantity . "')";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The stock side of the cost of sales GL posting could not be inserted because');
$DbgMsg = _('The following SQL to insert the GLTrans record was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
}
/* end of if GL and stock integrated and standard cost !=0 */
if ($_SESSION['CompanyRecord']['gllink_debtors'] == 1 and $ReturnItemLine->Price != 0) {
//Post sales transaction to GL credit sales
$SalesGLAccounts = GetSalesGLAccount($Area, $ReturnItemLine->StockID, $_SESSION['Items' . $identifier]->DefaultSalesType, $db);
$SQL = "INSERT INTO gltrans (type,\r\n\t\t\t\t\t\t\t\t\t\t\ttypeno,\r\n\t\t\t\t\t\t\t\t\t\t\ttrandate,\r\n\t\t\t\t\t\t\t\t\t\t\tperiodno,\r\n\t\t\t\t\t\t\t\t\t\t\taccount,\r\n\t\t\t\t\t\t\t\t\t\t\tnarrative,\r\n\t\t\t\t\t\t\t\t\t\t\tamount )\r\n\t\t\t\t\t\t\t\t\t\tVALUES ( 11,\r\n\t\t\t\t\t\t\t\t\t\t\t'" . $CreditNoteNo . "',\r\n\t\t\t\t\t\t\t\t\t\t\t'" . $ReturnDate . "',\r\n\t\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\r\n\t\t\t\t\t\t\t\t\t\t\t'" . $SalesGLAccounts['salesglcode'] . "',\r\n\t\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['Items' . $identifier]->DebtorNo . " - " . $ReturnItemLine->StockID . " x " . -$ReturnItemLine->Quantity . " @ " . $ReturnItemLine->Price . "',\r\n\t\t\t\t\t\t\t\t\t\t\t'" . $ReturnItemLine->Price * $ReturnItemLine->Quantity / $ExRate . "')";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The sales GL posting could not be inserted because');
$DbgMsg = '<br />' . _('The following SQL to insert the GLTrans record was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
if ($ReturnItemLine->DiscountPercent != 0) {
$SQL = "INSERT INTO gltrans (type,\r\n\t\t\t\t\t\t\t\t\t\t\t\ttypeno,\r\n\t\t\t\t\t\t\t\t\t\t\t\ttrandate,\r\n\t\t\t\t\t\t\t\t\t\t\t\tperiodno,\r\n\t\t\t\t\t\t\t\t\t\t\t\taccount,\r\n\t\t\t\t\t\t\t\t\t\t\t\tnarrative,\r\n\t\t\t\t\t\t\t\t\t\t\t\tamount )\r\n\t\t\t\t\t\t\t\t\t\t\t\tVALUES ( 11,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $CreditNoteNo . "',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $ReturnDate . "',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $SalesGLAccounts['discountglcode'] . "',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['Items' . $identifier]->DebtorNo . " - " . $ReturnItemLine->StockID . " @ " . $ReturnItemLine->DiscountPercent * 100 . "%',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . -($ReturnItemLine->Price * $ReturnItemLine->Quantity * $ReturnItemLine->DiscountPercent / $ExRate) . "')";
$SQL = "INSERT INTO stockmoves (\n\t\t\t\t\t\t\t\t\tstockid,\n\t\t\t\t\t\t\t\t\ttype,\n\t\t\t\t\t\t\t\t\ttransno,\n\t\t\t\t\t\t\t\t\tloccode,\n\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\tprd,\n\t\t\t\t\t\t\t\t\treference,\n\t\t\t\t\t\t\t\t\tqty,\n\t\t\t\t\t\t\t\t\tnewqoh)\n\t\t\t\t\t\t\t\tVALUES (\n\t\t\t\t\t\t\t\t\t'" . $StockID . "',\n\t\t\t\t\t\t\t\t\t17,\n\t\t\t\t\t\t\t\t\t'" . $AdjustmentNumber . "',\n\t\t\t\t\t\t\t\t\t'" . $Location . "',\n\t\t\t\t\t\t\t\t\t'" . $SQLAdjustmentDate . "',\n\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t'" . $Narrative . "',\n\t\t\t\t\t\t\t\t\t'" . -$Quantity . "',\n\t\t\t\t\t\t\t\t\t'" . ($QtyOnHandPrior - $Quantity) . "'\n\t\t\t\t\t\t\t\t)";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The stock movement record cannot be inserted because');
$DbgMsg = _('The following SQL to insert the stock movement record was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
/*Get the ID of the StockMove... */
$StkMoveNo = DB_Last_Insert_ID($db, 'stockmoves', 'stkmoveno');
$SQL = "UPDATE stockrequestitems\n\t\t\t\t\t\tSET qtydelivered=qtydelivered+" . $Quantity . "\n\t\t\t\t\t\tWHERE dispatchid='" . $RequestID . "'\n\t\t\t\t\t\t\tAND dispatchitemsid='" . $LineID . "'";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The location stock record could not be updated because');
$DbgMsg = _('The following SQL to update the stock record was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
$SQL = "UPDATE locstock SET quantity = quantity - '" . $Quantity . "'\n\t\t\t\t\t\t\t\t\tWHERE stockid='" . $StockID . "'\n\t\t\t\t\t\t\t\t\t\tAND loccode='" . $Location . "'";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The location stock record could not be updated because');
$DbgMsg = _('The following SQL to update the stock record was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
if ($_SESSION['CompanyRecord']['gllink_stock'] == 1 and $StandardCost > 0) {
$StockGLCodes = GetStockGLCode($StockID, $db);
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\t\tamount,\n\t\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\t\ttag)\n\t\t\t\t\t\t\t\t\t\t\tVALUES (17,\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $AdjustmentNumber . "',\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $SQLAdjustmentDate . "',\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $StockGLCodes['issueglact'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $StandardCost * $Quantity . "',\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $Narrative . "',\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $Tag . "'\n\t\t\t\t\t\t\t\t\t\t\t)";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The general ledger transaction entries could not be added because');
$DbgMsg = _('The following SQL to insert the GL entries was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\t\tamount,\n\t\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\t\ttag)\n\t\t\t\t\t\t\t\t\t\t\tVALUES (17,\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $AdjustmentNumber . "',\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $SQLAdjustmentDate . "',\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $StockGLCodes['stockact'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $StandardCost * -$Quantity . "',\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $Narrative . "',\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $Tag . "'\n\t\t\t\t\t\t\t\t\t\t\t)";
$Errmsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The general ledger transaction entries could not be added because');
$DbgMsg = _('The following SQL to insert the GL entries was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
}
if ($Quantity >= $RequestedQuantity or $Completed == True) {
$SQL = "UPDATE stockrequestitems\n\t\t\t\t\t\t\t\tSET completed=1\n\t\t\t\t\t\t\tWHERE dispatchid='" . $RequestID . "'\n\t\t\t\t\t\t\t\tAND dispatchitemsid='" . $LineID . "'";
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
}
$Result = DB_Txn_Commit($db);
$ConfirmationText = _('An internal stock request for') . ' ' . $StockID . ' ' . _('has been fulfilled from location') . ' ' . $Location . ' ' . _('for a quantity of') . ' ' . locale_number_format($Quantity, $DecimalPlaces);
$CostRecd = $StdCostRow[0];
$StdCostUnit = $StdCostRow[0] / $myrow['totqtyrecd'];
if ($ItemShipmentCost != 0) {
$Variance = $StdCostUnit - $ItemShipmentCost;
} else {
$Variance = 0;
}
$TotalShiptVariance += $Variance * $myrow['totqtyinvoiced'];
if ($StdCostUnit > 0) {
$VariancePercentage = number_format($Variance * 100 / $StdCostUnit);
} else {
$VariancePercentage = 100;
}
if (isset($_POST['Close']) and $Variance != 0) {
if ($_SESSION['CompanyRecord']['gllink_stock'] == 1) {
$StockGLCodes = GetStockGLCode($myrow['itemcode'], $db);
}
/*GL journals depend on the costing method used currently:
Standard cost - the price variance between the exisitng system cost and the shipment cost is taken as a variance
to the price varaince account
Weighted Average Cost - the price variance is taken to the stock account and the cost updated to ensure the GL
stock account ties up to the stock valuation
*/
if ($_SESSION['WeightedAverageCosting'] == 1) {
/* Do the WAvg journal and cost update */
/*
First off figure out the new weighted average cost Need the following data:
How many in stock now
The quantity being costed here - $myrow['qtyinvoiced']
The cost of these items - $ItemShipmentCost
} else {
/* insert a new sales analysis record */
$SQL = "INSERT INTO salesanalysis (\r\n\t\t\t\t\t\ttypeabbrev,\r\n\t\t\t\t\t\tperiodno,\r\n\t\t\t\t\t\tamt,\r\n\t\t\t\t\t\tcost,\r\n\t\t\t\t\t\tcust,\r\n\t\t\t\t\t\tcustbranch,\r\n\t\t\t\t\t\tqty,\r\n\t\t\t\t\t\tdisc,\r\n\t\t\t\t\t\tstockid,\r\n\t\t\t\t\t\tarea,\r\n\t\t\t\t\t\tbudgetoractual,\r\n\t\t\t\t\t\tsalesperson,\r\n\t\t\t\t\t\tstkcategory\r\n\t\t\t\t\t\t)\r\n\t\t\t\t\tSELECT '" . $_SESSION['Items']->DefaultSalesType . "',\r\n\t\t\t\t\t\t" . $PeriodNo . ",\r\n\t\t\t\t\t\t" . $OrderLine->Price * $OrderLine->QtyDispatched / $_SESSION['CurrencyRate'] . ",\r\n\t\t\t\t\t\t" . $OrderLine->StandardCost * $OrderLine->QtyDispatched . ",\r\n\t\t\t\t\t\t'" . $_SESSION['Items']->DebtorNo . "',\r\n\t\t\t\t\t\t'" . $_SESSION['Items']->Branch . "',\r\n\t\t\t\t\t\t" . $OrderLine->QtyDispatched . ",\r\n\t\t\t\t\t\t" . $OrderLine->DiscountPercent * $OrderLine->Price * $OrderLine->QtyDispatched / $_SESSION['CurrencyRate'] . ",\r\n\t\t\t\t\t\t'" . $OrderLine->StockID . "',\r\n\t\t\t\t\t\tcustbranch.area,\r\n\t\t\t\t\t\t1,\r\n\t\t\t\t\t\tcustbranch.salesman,\r\n\t\t\t\t\t\tstockmaster.categoryid\r\n\t\t\t\t\tFROM stockmaster,\r\n\t\t\t\t\t\tcustbranch\r\n\t\t\t\t\tWHERE stockmaster.stockid = '" . $OrderLine->StockID . "'\r\n\t\t\t\t\tAND custbranch.debtorno = '" . $_SESSION['Items']->DebtorNo . "'\r\n\t\t\t\t\tAND custbranch.branchcode='" . $_SESSION['Items']->Branch . "'";
}
$ErrMsg = _('Sales analysis record could not be added or updated because');
$DbgMsg = _('The following SQL to insert the sales analysis record was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
/* If GLLink_Stock then insert GLTrans to credit stock and debit cost of sales at standard cost*/
if ($_SESSION['CompanyRecord']['gllink_stock'] == 1 and $OrderLine->StandardCost != 0) {
/*first the cost of sales entry*/
$SQL = "INSERT INTO gltrans (\r\n\t\t\t\t\t\t\ttype,\r\n\t\t\t\t\t\t\ttypeno,\r\n\t\t\t\t\t\t\ttrandate,\r\n\t\t\t\t\t\t\tperiodno,\r\n\t\t\t\t\t\t\taccount,\r\n\t\t\t\t\t\t\tnarrative,\r\n\t\t\t\t\t\t\tamount\r\n\t\t\t\t\t\t\t)\r\n\t\t\t\t\tVALUES (\r\n\t\t\t\t\t\t10,\r\n\t\t\t\t\t\t" . $InvoiceNo . ",\r\n\t\t\t\t\t\t'" . $DefaultDispatchDate . "',\r\n\t\t\t\t\t\t" . $PeriodNo . ",\r\n\t\t\t\t\t\t" . GetCOGSGLAccount($Area, $OrderLine->StockID, $_SESSION['Items']->DefaultSalesType, $db) . ",\r\n\t\t\t\t\t\t'" . $_SESSION['Items']->DebtorNo . " - " . $OrderLine->StockID . " x " . $OrderLine->QtyDispatched . " @ " . $OrderLine->StandardCost . "',\r\n\t\t\t\t\t\t" . $OrderLine->StandardCost * $OrderLine->QtyDispatched . "\r\n\t\t\t\t\t)";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The cost of sales GL posting could not be inserted because');
$DbgMsg = _('The following SQL to insert the GLTrans record was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
/*now the stock entry*/
$StockGLCode = GetStockGLCode($OrderLine->StockID, $db);
$SQL = "INSERT INTO gltrans (\r\n\t\t\t\t\t\t\ttype,\r\n\t\t\t\t\t\t\ttypeno,\r\n\t\t\t\t\t\t\ttrandate,\r\n\t\t\t\t\t\t\tperiodno,\r\n\t\t\t\t\t\t\taccount,\r\n\t\t\t\t\t\t\tnarrative,\r\n\t\t\t\t\t\t\tamount)\r\n\t\t\t\t\tVALUES (\r\n\t\t\t\t\t\t10,\r\n\t\t\t\t\t\t" . $InvoiceNo . ",\r\n\t\t\t\t\t\t'" . $DefaultDispatchDate . "',\r\n\t\t\t\t\t\t" . $PeriodNo . ",\r\n\t\t\t\t\t\t" . $StockGLCode['stockact'] . ",\r\n\t\t\t\t\t\t'" . $_SESSION['Items']->DebtorNo . " - " . $OrderLine->StockID . " x " . $OrderLine->QtyDispatched . " @ " . $OrderLine->StandardCost . "',\r\n\t\t\t\t\t\t" . -$OrderLine->StandardCost * $OrderLine->QtyDispatched . "\r\n\t\t\t\t\t)";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The stock side of the cost of sales GL posting could not be inserted because');
$DbgMsg = _('The following SQL to insert the GLTrans record was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
}
/* end of if GL and stock integrated and standard cost !=0 */
if ($_SESSION['CompanyRecord']['gllink_debtors'] == 1 and $OrderLine->Price != 0) {
//Post sales transaction to GL credit sales
$SalesGLAccounts = GetSalesGLAccount($Area, $OrderLine->StockID, $_SESSION['Items']->DefaultSalesType, $db);
$SQL = "INSERT INTO gltrans (\r\n\t\t\t\t\t\t\ttype,\r\n\t\t\t\t\t\t\ttypeno,\r\n\t\t\t\t\t\t\ttrandate,\r\n\t\t\t\t\t\t\tperiodno,\r\n\t\t\t\t\t\t\taccount,\r\n\t\t\t\t\t\t\tnarrative,\r\n\t\t\t\t\t\t\tamount\r\n\t\t\t\t\t\t)\r\n\t\t\t\t\tVALUES (\r\n\t\t\t\t\t\t10,\r\n\t\t\t\t\t\t" . $InvoiceNo . ",\r\n\t\t\t\t\t\t'" . $DefaultDispatchDate . "',\r\n\t\t\t\t\t\t" . $PeriodNo . ",\r\n\t\t\t\t\t\t" . $SalesGLAccounts['salesglcode'] . ",\r\n\t\t\t\t\t\t'" . $_SESSION['Items']->DebtorNo . " - " . $OrderLine->StockID . " x " . $OrderLine->QtyDispatched . " @ " . $OrderLine->Price . "',\r\n\t\t\t\t\t\t" . -$OrderLine->Price * $OrderLine->QtyDispatched / $_SESSION['CurrencyRate'] . "\r\n\t\t\t\t\t)";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The sales GL posting could not be inserted because');
$DbgMsg = '<br>' . _('The following SQL to insert the GLTrans record was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
if ($OrderLine->DiscountPercent != 0) {
$SQL = "INSERT INTO gltrans (\r\n\t\t\t\t\t\t\ttype,\r\n\t\t\t\t\t\t\ttypeno,\r\n\t\t\t\t\t\t\ttrandate,\r\n\t\t\t\t\t\t\tperiodno,\r\n\t\t\t\t\t\t\taccount,\r\n\t\t\t\t\t\t\tnarrative,\r\n\t\t\t\t\t\t\tamount\r\n\t\t\t\t\t\t)\r\n\t\t\t\t\t\tVALUES (\r\n\t\t\t\t\t\t\t10,\r\n\t\t\t\t\t\t\t" . $InvoiceNo . ",\r\n\t\t\t\t\t\t\t'" . $DefaultDispatchDate . "',\r\n\t\t\t\t\t\t\t" . $PeriodNo . ",\r\n\t\t\t\t\t\t\t" . $SalesGLAccounts['discountglcode'] . ",\r\n\t\t\t\t\t\t\t'" . $_SESSION['Items']->DebtorNo . " - " . $OrderLine->StockID . " @ " . $OrderLine->DiscountPercent * 100 . "%',\r\n\t\t\t\t\t\t\t" . $OrderLine->Price * $OrderLine->QtyDispatched * $OrderLine->DiscountPercent / $_SESSION['CurrencyRate'] . "\r\n\t\t\t\t\t\t)";
$ErrMsg = _('Could not retrieve QOH at the destination because');
$Result = DB_query($SQL, $ErrMsg, $DbgMsg, true);
if (DB_num_rows($Result) == 1) {
$LocQtyRow = DB_fetch_row($Result);
$QtyOnHandPrior = $LocQtyRow[0];
} else {
// There must actually be some error this should never happen
$QtyOnHandPrior = 0;
}
// Insert incoming inventory GL transaction if any of the locations has a GL account code:
if ($_SESSION['Transfer']->StockLocationFromAccount != '' or $_SESSION['Transfer']->StockLocationToAccount != '') {
// Get the account code:
if ($_SESSION['Transfer']->StockLocationToAccount != '') {
$AccountCode = $_SESSION['Transfer']->StockLocationToAccount;
} else {
$StockGLCode = GetStockGLCode($_SESSION['Transfer']->TransferItem[0]->StockID, $db);
// Get Category's account codes.
$AccountCode = $StockGLCode['stockact'];
// Select account code for stock.
}
// Get the item cost:
$SQLstandardcost = "SELECT stockmaster.materialcost + stockmaster.labourcost + stockmaster.overheadcost AS standardcost\n\t\t\t\t\t\t\t\tFROM stockmaster\n\t\t\t\t\t\t\t\tWHERE stockmaster.stockid ='" . $_SESSION['Transfer']->TransferItem[0]->StockID . "'";
$ErrMsg = _('The standard cost of the item cannot be retrieved because');
$DbgMsg = _('The SQL that failed was');
$myrow = DB_fetch_array(DB_query($SQLstandardcost, $ErrMsg, $DbgMsg));
$StandardCost = $myrow['standardcost'];
// QUESTION: Standard cost for: Assembly (value="A") and Manufactured (value="M") items ?
// Insert record:
$SQL = "INSERT INTO gltrans (\n\t\t\t\t\tperiodno,\n\t\t\t\t\ttrandate,\n\t\t\t\t\ttype,\n\t\t\t\t\ttypeno,\n\t\t\t\t\taccount,\n\t\t\t\t\tnarrative,\n\t\t\t\t\tamount)\n\t\t\t\tVALUES ('" . $PeriodNo . "','" . $SQLTransferDate . "',\n\t\t\t\t\t16,'" . $TransferNumber . "','" . $AccountCode . "','" . $_SESSION['Transfer']->StockLocationTo . ' - ' . $_SESSION['Transfer']->TransferItem[0]->StockID . ' x ' . $_SESSION['Transfer']->TransferItem[0]->Quantity . ' @ ' . $StandardCost . "','" . $_SESSION['Transfer']->TransferItem[0]->Quantity * $StandardCost . "')";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The incoming inventory GL transacction record could not be inserted because');
$DbgMsg = _('The following SQL to insert records was used');
