Table of Contents

{$page}

Explanation of SQL

To export the APs to Quickbooks requires two queries to be run, saved, and imported.

Risk of Data Corruption if Run Improperly

None. This is a selection query and no data is modified in Cyrious in the running of it.

SQL

Vendors with Open Bills

code_formatsql

Select Account.CompanyName as [Vendor], 

     Bill.BillNumber as [Bill Number],  
     Bill.PONumber as [Reference Number], 
     Bill.DueDate as [Due Date], 
     Bill.OrderCreatedDate as [Bill Date], 
     Bill.SaleDate as [Invoice Date], 
     Bill.BalanceDue as [Balance Due],
     Account.AccountNumber as [Company Number]

from TransHeader Bill

left join Account on Bill.AccountID = Account.ID

where TransactionType=8 and StatusID = 6

code

Open Bills

code_formatsql

select Account.CompanyName as [Vendor], 

     Account.AccountNumber as [Company Number],
     Address.FormattedText as [Address],
     coalesce(PC.FirstName + ' ' + PC.LastName, '') as [Contact],
     coalesce(PC.PrimaryNumber, Account.PrimaryNumber, '') as [Phone Number],
     coalesce(PC.SecondaryNumber, Account.SecondaryNumber, '') as [Fax Number],
     (Select SUM(BalanceDue) from TransHeader Bill where TransactionType=8 and StatusID = 6 and Bill.AccountID = Account.ID) as [Balance Outstanding]

from Account

left join Address on Account.BillingAddressID = Address.ID

left join AccountContact PC on Account.PrimaryContactID = PC.ID

where Account.ID in (Select AccountID from TransHeader where TransactionType=8 and StatusID = 6)

code

Version Information