Nav view search

Navigation

Search

Knowledge Base

Technical tips, helpful recommendations, pointers and fixes.

Back

How to Bill All Closed Jobs

By Justin Bierman 2012-07-13

Bookmark and Share Share

This article displays a scripted method for setting a billed status to all closed jobs.

IMPORTANT: YOU SHOULD PERFORM A COMPLETE BACKUP OF ALL USER AND SYSTEM DATABASES BEFORE PERFORMING ANY PROCEDURE IN THE ARTICLE.

Things to keep in mind before executing this script:

  1. This script will set the status of ALL closed Jobs to Billed.
  2. No Invoices will be created.
  3. After running the script it will not be possible to reopen or to create Invoices for all closed, unbilled, Jobs.
  4. This script is to be run ONLY once.
  5. It is strongly suggested you make a full backup of your Database.
  6. Apply this script to the database in which you want to have closed jobs billed.

To set all closed jobs to a status of Billed:

  1. Open the Microsoft SQL Server Query Analyzer. (Start/Programs/Microsoft SQL Server/SQL Query Analyzer)
  2. Enter the appropriate login and password to connect to the appropriate SQL Server.
  3. In the Query Analyzer, choose the database in which you would like to have all closed job billed.
  4. Copy and paste the script below into the Query Analyzer:

  5. BEGIN TRANSACTION 
     
    UPDATE order_entry 
    SET    billed_job = 1, 
           billed_date = Getdate() 
    WHERE  closed_job = 1 
           AND billed_job = 0 
     
    COMMIT TRANSACTION 

  6. Run the query.