Care2007/modules/weberp/ReverseGRN.php

   1 <?php
   2
   3
   4 /* $Revision: 1.16 $ */
   5
   6 $PageSecurity = 11;
   7
   8 /* Session started in header.inc for password checking and authorisation level check */
   9
  10 include('includes/DefineSerialItems.php');
  11 include('includes/SQL_CommonFunctions.inc');
  12 include('includes/session.inc');
  13
  14 $title = _('Reverse Goods Received');
  15
  16 include('includes/header.inc');
  17
  18 if ($_SESSION['SupplierID']!="" AND isset($_SESSION['SupplierID']) AND !isset($_POST['SupplierID']) OR $_POST['SupplierID']==""){
  19         $_POST['SupplierID']=$_SESSION['SupplierID'];
  20 }
  21 if (!isset($_POST['SupplierID']) OR $_POST['SupplierID']==""){
  22         echo '<BR>' . _('This page is expected to be called after a supplier has been selected');
  23         echo "<META HTTP-EQUIV='Refresh' CONTENT='0; URL=" . $rootpath . '/SelectSupplier.php?' . SID . "'>";
  24         exit;
  25 } elseif ($_POST['SuppName']=="" OR !isset($_POST['SuppName'])) {
  26         $sql = "SELECT suppname FROM suppliers WHERE supplierid='" . $_SESSION['SupplierID'] . "'";
  27         $SuppResult = DB_query($sql,$db, _('Could not retrieve the supplier name for') . ' ' . $_SESSION['SupplierID']);
  28         $SuppRow = DB_fetch_row($SuppResult);
  29         $_POST['SuppName'] = $SuppRow[0];
  30 }
  31
  32 echo '<CENTER><FONT SIZE=4><B><U>' . _('Reverse Goods Received from') . ' ' . $_POST['SuppName'] . ' </U></B></FONT></CENTER><BR>';
  33
  34 if (isset($_GET['GRNNo']) AND isset($_POST['SupplierID'])){
  35 /* SQL to process the postings for the GRN reversal.. */
  36
  37         //Get the details of the GRN item and the cost at which it was received and other PODetail info
  38         $SQL = "SELECT grns.podetailitem,
  39                         grns.grnbatch,
  40                         grns.itemcode,
  41                         grns.itemdescription,
  42                         grns.deliverydate,
  43                         purchorderdetails.glcode,
  44                         grns.qtyrecd,
  45                         grns.quantityinv,
  46                         purchorderdetails.stdcostunit,
  47                         purchorders.intostocklocation,
  48                         purchorders.orderno
  49                 FROM grns, purchorderdetails, purchorders
  50                 WHERE grns.podetailitem=purchorderdetails.podetailitem
  51                 AND purchorders.orderno = purchorderdetails.orderno
  52                 AND grnno=" . (int) $_GET['GRNNo'];
  53
  54         $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('Could not get the details of the GRN selected for reversal because') . ' ';
  55         $DbgMsg = _('The following SQL to retrieve the GRN details was used') . ':';
  56
  57         $Result=DB_query($SQL,$db,$ErrMsg,$DbgMsg);
  58
  59         $GRN = DB_fetch_array($Result);
  60         $QtyToReverse = $GRN['qtyrecd'] - $GRN['quantityinv'];
  61
  62         if ($QtyToReverse ==0){
  63                 echo '<BR><BR>' . _('The GRN') . ' ' . $_GET['GRNNo'] . ' ' . _('has already been reversed or fully invoiced by the supplier - it cannot be reversed - stock quantities must be corrected by stock adjustments - the stock is paid for');
  64                 include ('includes/footer.inc');
  65                 exit;
  66         }
  67
  68         /*If the item is a stock item then need to check for Controlled or not ...
  69          if its controlled then need to check existence of the controlled items
  70          that came in with this GRN */
  71
  72
  73         $SQL = "SELECT stockmaster.controlled
  74                         FROM stockmaster WHERE stockid ='" . $GRN['itemcode'] . "'";
  75         $CheckControlledResult = DB_query($SQL,$db,'<BR>' . _('Could not determine if the item was controlled or not because') . ' ');
  76         $ControlledRow = DB_fetch_row($CheckControlledResult);
  77         if ($ControlledRow[0]==1) { /*Then its a controlled item */
  78                 $Controlled = true;
  79                 /*So check to ensure the serial items received on this GRN are still there */
  80                 /*First get the StockMovement Reference for the GRN */
  81                 $SQL = "SELECT stockserialmoves.serialno,
  82                                 stockserialmoves.moveqty
  83                         FROM stockmoves INNER JOIN stockserialmoves
  84                                 ON stockmoves.stkmoveno= stockserialmoves.stockmoveno
  85                         WHERE stockmoves.stockid='" . $GRN['itemcode'] . "'
  86                         AND stockmoves.type =25
  87                         AND stockmoves.transno=" . $GRN['grnbatch'];
  88                 $GetStockMoveResult = DB_query($SQL,$db,_('Could not retrieve the stock movement reference number which is required in order to retrieve details of the serial items that came in with this GRN'));
  89
  90                 while ($SerialStockMoves = DB_fetch_array($GetStockMoveResult)){
  91
  92                         $SQL = "SELECT stockserialitems.quantity
  93                                 FROM stockserialitems
  94                                 WHERE stockserialitems.stockid='" . $GRN['itemcode'] . "'
  95                                 AND stockserialitems.loccode ='" . $GRN['intostocklocation'] . "'
  96                                 AND stockserialitems.serialno ='" . $SerialStockMoves['serialno'] . "'";
  97                         $GetQOHResult = DB_query($SQL,$db,_('Unable to retrieve the quantity on hand of') . ' ' . $GRN['itemcode'] . ' ' . _('for Serial No') . ' ' . $SerialStockMoves['serialno']);
  98                         $GetQOH = DB_fetch_row($GetQOHResult);
  99                         if ($GetQOH[0] < $SerialStockMoves['moveqty']){
 100                                 /*Then some of the original goods received must have been sold
 101                                 or transfered so cannot reverse the GRN */
 102                                 prnMsg(_('Unfortunately, of the original number') . ' (' . $SerialStockMoves['moveqty'] . ') ' . _('that were received on serial number') . ' ' . $SerialStockMoves['serialno'] . ' ' . _('only') . ' ' . $GetQOH[0] . ' ' . _('remain') . '. ' . _('The GRN can only be reversed if all the original serial number items are still in stock in the location they were received into'),'error');
 103                                 include ('includes/footer.inc');
 104                                 exit;
 105                         }
 106                 }
 107                 /*reset the pointer on this resultset ... will need it later */
 108                 DB_data_seek($GetStockMoveResult,0);
 109         } else {
 110                 $Controlled = false;
 111         }
 112
 113 /*Start an SQL transaction */
 114
 115         $Result = DB_query("BEGIN",$db);
 116
 117         $PeriodNo = GetPeriod(ConvertSQLDate($GRN['deliverydate']), $db);
 118
 119 /*Now the SQL to do the update to the PurchOrderDetails */
 120
 121         $SQL = "UPDATE purchorderdetails
 122                 SET quantityrecd = quantityrecd - " . $QtyToReverse . ",
 123                 completed=0
 124                 WHERE purchorderdetails.podetailitem = " . $GRN['podetailitem'];
 125
 126         $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The purchase order detail record could not be updated with the quantity reversed because');
 127         $DbgMsg = _('The following SQL to update the purchase order detail record was used');
 128         $Result=DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
 129
 130 /*Need to update the existing GRN item */
 131
 132         $SQL = "UPDATE grns
 133                 SET qtyrecd = qtyrecd - $QtyToReverse
 134                 WHERE grns.grnno=" . $_GET['GRNNo'];
 135
 136         $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The GRN record could not be updated') . '. ' . _('This reversal of goods received has not been processed because');
 137         $DbgMsg = _('The following SQL to insert the GRN record was used');
 138         $Result=DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
 139
 140         $SQL = "SELECT stockmaster.controlled
 141                 FROM stockmaster
 142                 WHERE stockmaster.stockid = '" . $GRN['itemcode'] . "'";
 143         $Result = DB_query($SQL, $db, _('Could not determine if the item exists because'),'<BR>' . _('The SQL that failed was') . ' ',true);
 144
 145         if (DB_num_rows($Result)==1){ /* if the GRN is in fact a stock item being reversed */
 146
 147                 $StkItemExists = DB_fetch_row($Result);
 148                 $Controlled = $StkItemExists[0];
 149
 150         /* Update location stock records - NB  a PO cannot be entered for a dummy/assembly/kit parts */
 151         /*Need to get the current location quantity will need it later for the stock movement */
 152                 $SQL="SELECT quantity
 153                         FROM locstock
 154                         WHERE stockid='" . $GRN['itemcode'] . "'
 155                         AND loccode= '" . $GRN['intostocklocation'] . "'";
 156
 157                 $Result = DB_query($SQL, $db, _('Could not get the quantity on hand of the item before the reversal was processed'),_('The SQL that failed was'),true);
 158                 if (DB_num_rows($Result)==1){
 159                         $LocQtyRow = DB_fetch_row($Result);
 160                         $QtyOnHandPrior = $LocQtyRow[0];
 161                 } else {
 162                 /*There must actually be some error this should never happen */
 163                         $QtyOnHandPrior = 0;
 164                 }
 165
 166                 $SQL = "UPDATE locstock
 167                         SET quantity = quantity - " . $QtyToReverse . "
 168                         WHERE stockid = '" . $GRN['itemcode'] . "'
 169                         AND loccode = '" . $GRN['intostocklocation'] . "'";
 170
 171                 $ErrMsg =  _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The location stock record could not be updated because');
 172                 $DbgMsg = _('The following SQL to update the location stock record was used');
 173                 $Result=DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
 174
 175         /* If its a stock item .... Insert stock movements - with unit cost */
 176
 177                 $SQL = "INSERT INTO stockmoves (
 178                                 stockid,
 179                                 type,
 180                                 transno,
 181                                 loccode,
 182                                 trandate,
 183                                 prd,
 184                                 reference,
 185                                 qty,
 186                                 standardcost,
 187                                 newqoh)
 188                         VALUES (
 189                                 '" . $GRN['itemcode'] . "',
 190                                 25,
 191                                 " . $_GET['GRNNo'] . ",
 192                                 '" . $GRN['intostocklocation'] . "',
 193                                 '" . $GRN['deliverydate'] . "',
 194                                 " . $PeriodNo . ",
 195                                 '" . _('Reversal') . ' - ' . $_POST['SupplierID'] . ' - ' . $GRN['orderno'] . "',
 196                                 " . -$QtyToReverse . ',
 197                                 ' . $GRN['stdcostunit'] . ',
 198                                 ' . ($QtyOnHandPrior - $QtyToReverse) . '
 199                                 )';
 200
 201                 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('Stock movement records could not be inserted because');
 202                 $DbgMsg = _('The following SQL to insert the stock movement records was used');
 203                 $Result=DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
 204
 205                 $StkMoveNo = DB_Last_Insert_ID($db,'stockmoves','stkmoveno');
 206
 207                 if ($Controlled==true){
 208                         while ($SerialStockMoves = DB_fetch_array($GetStockMoveResult)){
 209                                 $SQL = "INSERT INTO stockserialmoves (
 210                                                 stockmoveno,
 211                                                 stockid,
 212                                                 serialno,
 213                                                 moveqty)
 214                                         VALUES (
 215                                                 " . $StkMoveNo . ",
 216                                                 '" . $GRN['itemcode'] . "',
 217                                                 '" . $SerialStockMoves['serialno'] . "',
 218                                                 " . -$SerialStockMoves['moveqty'] . ")";
 219                                 $result = DB_query($SQL,$db,_('Could not insert the reversing stock movements for the batch/serial numbers'),_('The SQL used but failed was') . ':',true);
 220
 221                                 $SQL = "UPDATE stockserialitems
 222                                         SET quantity=quantity - " . $SerialStockMoves['moveqty'] . "
 223                                         WHERE stockserialitems.stockid='" . $GRN['itemcode'] . "'
 224                                         AND stockserialitems.loccode ='" . $GRN['intostocklocation'] . "'
 225                                         AND stockserialitems.serialno = '" . $SerialStockMoves['serialno'] . "'";
 226                                 $result = DB_query($SQL,$db,_('Could not update the batch/serial stock records'),_('The SQL used but failed was') . ':',true);
 227                         }
 228                 }
 229         } /*end of its a stock item - updates to locations and insert movements*/
 230
 231 /* If GLLink_Stock then insert GLTrans to debit the GL Code  and credit GRN Suspense account at standard cost*/
 232
 233         if ($_SESSION['CompanyRecord']['gllink_stock']==1 AND $GRN['glcode'] !=0 AND $GRN['stdcostunit']!=0){ /*GLCode is set to 0 when the GLLink is not activated
 234         this covers a situation where the GLLink is now active  but it wasn't when this PO was entered */
 235         /*first the credit using the GLCode in the PO detail record entry*/
 236
 237                 $SQL = "INSERT INTO gltrans (
 238                                 type,
 239                                 typeno,
 240                                 trandate,
 241                                 periodno,
 242                                 account,
 243                                 narrative,
 244                                 amount)
 245                         VALUES (
 246                                 25,
 247                                 " . $_GET['GRNNo'] . ",
 248                                 '" . $GRN['deliverydate'] . "',
 249                                 " . $PeriodNo . ",
 250                                 " . $GRN['glcode'] . ",
 251                                 '" . _('GRN Reversal for PO') .": " . $GRN['orderno'] . " " . $_POST['SupplierID'] . " - " . $GRN['itemcode'] . "-" . $GRN['itemdescription'] . " x " . $QtyToReverse . " @ " . number_format($GRN['stdcostunit'],2) . "',
 252                                 " . -($GRN['stdcostunit'] * $QtyToReverse) . "
 253                                 )";
 254
 255                 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The purchase GL posting could not be inserted for the reversal of the received item because');
 256                 $DbgMsg = _('The following SQL to insert the purchase GLTrans record was used');
 257                 $Result=DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
 258
 259 /*now the GRN suspense entry*/
 260                 $SQL = "INSERT INTO gltrans (
 261                                 type,
 262                                 typeno,
 263                                 trandate,
 264                                 periodno,
 265                                 account,
 266                                 narrative,
 267                                 amount)
 268                         VALUES (
 269                                 25,
 270                                 " . $_GET['GRNNo'] . ",
 271                                 '" . $GRN['deliverydate'] . "',
 272                                 " . $PeriodNo . ",
 273                                 " . $_SESSION['CompanyRecord']['grnact'] . ", '"
 274                                 . _('GRN Reversal PO') . ': ' . $GRN['orderno'] . " " . $_POST['SupplierID'] . " - " . $GRN['itemcode'] . "-" . $GRN['itemdescription'] . " x " . $QtyToReverse . " @ " . number_format($GRN['stdcostunit'],2) . "',
 275                                 " . $GRN['stdcostunit'] * $QtyToReverse . "
 276                                 )";
 277
 278                 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The GRN suspense side of the GL posting could not be inserted because');
 279                 $DbgMsg = _('The following SQL to insert the GRN Suspense GLTrans record was used');
 280                 $Result=DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
 281          } /* end of if GL and stock integrated*/
 282
 283         $SQL="COMMIT";
 284         $Result = DB_query($SQL,$db);
 285
 286         echo '<BR>' . _('GRN number') . ' ' . $_GET['GRNNo'] . ' ' . _('for') . ' ' . $QtyToReverse . ' x ' . $GRN['itemcode'] . ' - ' . $GRN['itemdescription'] . ' ' . _('has been reversed') . '<BR>';
 287         unset($_GET['GRNNo']);  // to ensure it cant be done again!!
 288         echo '<A HREF="' . $_SERVER['PHP_SELF'] . '?' . SID . '">' . _('Select another GRN to Reverse') . '</A>';
 289 /*end of Process Goods Received Reversal entry */
 290
 291 } else {
 292         echo '<FORM ACTION="' . $_SERVER['PHP_SELF'] . '?' . SID . '" METHOD=POST>';
 293
 294         if (!isset($_POST['RecdAfterDate']) OR !Is_Date($_POST['RecdAfterDate'])) {
 295                 $_POST['RecdAfterDate'] = Date($_SESSION['DefaultDateFormat'],Mktime(0,0,0,Date("m")-3,Date("d"),Date("Y")));
 296         }
 297
 298         echo '<INPUT TYPE=HIDDEN NAME="SupplierID" VALUE="' . $_POST['SupplierID'] . '">';
 299         echo '<INPUT TYPE=HIDDEN NAME="SuppName" VALUE="' . $_POST['SuppName'] . '">';
 300         echo _('Show all goods received after') . ': <INPUT type=text name="RecdAfterDate" Value="' . $_POST['RecdAfterDate'] . '" MAXLENGTH =10 SIZE=10>
 301                 <INPUT TYPE=SUBMIT NAME="ShowGRNS" VALUE=' . _('Show Outstanding Goods Received') . '>';
 302
 303
 304         if (isset($_POST['ShowGRNS'])){
 305
 306                 $sql = "SELECT grnno,
 307                                 itemcode,
 308                                 itemdescription,
 309                                 deliverydate,
 310                                 qtyrecd,
 311                                 quantityinv,
 312                                 qtyrecd-quantityinv AS qtytoreverse
 313                         FROM grns
 314                         WHERE grns.supplierid = '" . $_POST['SupplierID'] . "'
 315                         AND (grns.qtyrecd-grns.quantityinv) >0";
 316
 317                 $ErrMsg = _('An error occurred in the attempt to get the outstanding GRNs for') . ' ' . $_POST['SuppName'] . '. ' . _('The message was') . ':';
 318                 $DbgMsg = _('The SQL that failed was') . ':';
 319                 $result = DB_query($sql,$db,$ErrMsg,$DbgMsg);
 320
 321                 if (DB_num_rows($result) ==0){
 322                         prnMsg(_('There are no outstanding goods received yet to be invoiced for') . ' ' . $_POST['SuppName'] . '.<BR>' . _('To reverse a GRN that has been invoiced first it must be credited'),'warn');
 323                 } else { //there are GRNs to show
 324
 325                         echo '<CENTER><TABLE CELLPADDING=2 COLSPAN=7 BORDER=0>';
 326                         $TableHeader = '<TR>
 327                                         <TD class="tableheader">' . _('GRN') . ' #</TD>
 328                                         <TD class="tableheader">' . _('Item Code') . '</TD>
 329                                         <TD class="tableheader">' . _('Description') . '</TD>
 330                                         <TD class="tableheader">' . _('Date') . '<BR>' . _('Received') . '</TD>
 331                                         <TD class="tableheader">' . _('Quantity') . '<BR>' . _('Received') . '</TD>
 332                                         <TD class="tableheader">' . _('Quantity') . '<BR>' . _('Invoiced') . '</TD>
 333                                         <TD class="tableheader">' . _('Quantity To') . '<BR>' . _('Reverse') . '</TD>
 334                                         </TR>';
 335
 336                         echo $TableHeader;
 337
 338                         /* show the GRNs outstanding to be invoiced that could be reversed */
 339                         $RowCounter =0;
 340                         while ($myrow=DB_fetch_array($result)) {
 341                                 if ($k==1){
 342                                         echo '<tr bgcolor="#CCCCCC">';
 343                                         $k=0;
 344                                 } else {
 345                                         echo '<tr bgcolor="#EEEEEE">';
 346                                         $k=1;
 347                                 }
 348
 349                                 $DisplayQtyRecd = number_format($myrow['qtyrecd'],2);
 350                                 $DisplayQtyInv = number_format($myrow['quantityinv'],2);
 351                                 $DisplayQtyRev = number_format($myrow['qtytoreverse'],2);
 352                                 $DisplayDateDel = ConvertSQLDate($myrow['deliverydate']);
 353                                 $LinkToRevGRN = '<A HREF="' . $_SERVER['PHP_SELF'] . '?' . SID . '&GRNNo=' . $myrow['grnno'] . '">' . _('Reverse') . '</A>';
 354
 355                                 printf("<TD>%s</TD>
 356                                         <TD>%s</TD>
 357                                         <TD>%s</TD>
 358                                         <TD>%s</TD>
 359                                         <TD ALIGN=RIGHT>%s</TD>
 360                                         <TD ALIGN=RIGHT>%s</TD>
 361                                         <TD ALIGN=RIGHT>%s</TD>
 362                                         <TD>%s</TD>
 363                                         </TR>",
 364                                         $myrow['grnno'],
 365                                         $myrow['itemcode'],
 366                                         $myrow['itemdescription'],
 367                                         $DisplayDateDel,
 368                                         $DisplayQtyRecd,
 369                                         $DisplayQtyInv,
 370                                         $DisplayQtyRev,
 371                                         $LinkToRevGRN);
 372
 373                                 $RowCounter++;
 374                                 if ($RowCounter >20){
 375                                         $RowCounter =0;
 376                                         echo $TableHeader;
 377                                 }
 378                         }
 379
 380                         echo '</TABLE>';
 381                 }
 382         }
 383 }
 384 include ('includes/footer.inc');
 385 ?>