How to update a Purchase Invoice document date

Overview

You need to enter a payment for a Purchase Order that was already received and converted into Purchase Receipts & Purchase Invoices on a given date, but the payment date is previous to the registered Document Date. Everest doesn't allow the Payment Date to be set to a value earlier than the Document Date.

Solution

The only way to work around the limitation will be to manually update the ORDER_DATE column in the PO table to the date equal or before the desired payment date.

To do so, please perform the following steps:

1. Open SQL Server Management Studio on the server system. 
2. In the left Object Explorer window, use the navigation tree to find your company Everest database.
3. Right-click on the database name and select New Query.
4. Run the following query into the new query window:
   

   DECLARE @docNo AS VARCHAR(20)
   DECLARE @docType AS INT
   DECLARE @oldDate AS DATE
   DECLARE @newDate AS DATE
   
   -----------------------------
   SET @docNo = '300156'
   SET @docType = 3
   SET @oldDate = '2013-07-01'
   SET @newDate = '2013-05-29'
   
   -----------------------------
   DECLARE @batchNo AS INT
   DECLARE @tranNo AS INT
   DECLARE @batchType AS INT
   
   SELECT @batchNo = batch_no,
          @tranNo = tran_no
   FROM   bch_tran
   WHERE  doc_no = @docNo
          AND doc_type = @docType
          AND entry_date = @oldDate
   
   SELECT @batchType = batch_type
   FROM   bch_head
   WHERE  batch_no = @batchNo
          AND tran_no = @tranNo
          AND tran_date = @oldDate
   
   UPDATE bch_tran
   SET    entry_date = @newDate
   WHERE  doc_no = @docNo
          AND doc_type = @docType
   
   UPDATE bch_head
   SET    tran_date = @newDate
   WHERE  batch_no = @batchNo
          AND tran_no = @tranNo
          AND tran_date = @oldDate
   
   UPDATE batch
   SET    period = Month(@newDate)
   WHERE  batch_no = @batchNo
          AND batch_type = @batchType
   
   UPDATE tranhist
   SET    ref_date = @newDate
   WHERE  doc_no = @docno
          AND doc_type = @docType
          AND ref_date = @oldDate
   
   UPDATE invhist
   SET    date_fld = @newDate
   WHERE  doc_type = @docType
          AND doc_no = @docNo
          AND date_fld = @oldDate
   
   UPDATE po
   SET    order_date = @newDate
   WHERE  status = @docType
          AND order_no = @docNo
          AND order_date = @oldDate
   
   UPDATE trck_sel
   SET    doc_date = @newDate
   WHERE  doc_date = @oldDate
          AND sub_code = @docNo
          AND doc_date = @oldDate 
   

5. Change the following parameters before executing the query:
   • @docNo: the document number. Needs to be updated as required; i.e. if the Purchase Invoice number is PI-110734, then the @docNo will be 110734. 
   • @docType: the document type, use 3 for Purchase Invoice and 9 for Sales Invoice, this has placed as a default in the script. No change required. 
   • @oldDate: the date of the document that needs to be changed, has been placed as a default to '2013-07-01', - needs to be changed as required.
   • @newDate: the correct date that needs to be adjusted to, defaulted to '2013-05-28' - needs to be changed as required.

Testing

Run the following query to confirm that the dates have been updated:

SELECT doc_no
FROM   po
WHERE  doc_no = {doc_no} 

Replace {doc_no} with the corresponding document number, as described above.