How to update a Purchase Invoice document date


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.


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 @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.


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.



Please sign in to leave a comment.