Accounts Payable Report Queries

AP Year End - Distribution Breakdown for Open Invoices

AP Year End - Top 10 Vendors

This query returns a breakdown of all distributions for open invoices as of the end of the year. It is useful for determining acrual entries required by companies filing taxes on a cash basis.

select i.vend_id "Vendor" ,v.name "Name",i.invoice_date "Inv Dt",i.invoice_no "Invoice NO",i.gross_amount "Inv Amt",i.invoice_bal "Inv Bal",dt.Description "Description",exp_gl "Dist GL" , a.description "Act Name",   (I.INVOICE_BAL/I.GROSS_AMOUNT)*DIST_AMT "Open Dist"
from appinv_aging(:YE,:YE,0) i
inner join aptinvdt dt on (dt.session_no=I.AP_session_no) and (dt.trans_no=I.AP_trans_no)
inner join aptvend v on v.vend_id=i.vend_id
inner join gltacct a on a.gl_account=dt.exp_gl

Formatting:

Vendor,Name: Break and Group 1

Invoice Date, Invoice No, Inv Amt, Inv Amt: Break and Group 2

Formatting the date as mm/dd/yy and setting field widths down from default improve printing.

AP 1099 List

Select CO.COMPANY_NAME,VEND_ID,NAME,NAME_2,ADDRESS_1,ADDRESS_2,CITY,STATE,ZIP,PAYMENT_TERMS,TEN99_CLASS,TEN99_FLAG,PHONE_NO,FAX_NO,START_DATE,TAX_CODE,TEN99_ADDRESS_1,TEN99_ADDRESS_2,TEN99_CITY,TEN99_STATE,TEN99_ZIP,TAX_ID,TAX_ID_TYPE,ACTIVE_FLAG,LAST_PURCHASE_DATE,LAST_PAY_DATE,LAST_CHECK_NO,YTD_PURCHASES,YTD_PAYMENTS,LAST_YEAR_PURCHASES,LAST_YEAR_PAYMENTS,
case tax_id_type WHEN 'I' THEN 'Individual/Sole proprietor' 
WHEN 'C' THEN 'C Corporation' 
WHEN 'S' THEN 'S Corporation' 
WHEN 'P' THEN 'Partnership' 
WHEN '3' THEN 'Trust/Estate' 
WHEN '4' THEN 'LLC C Corporation' 
WHEN 'T' THEN 'LLC S Corporation' 
WHEN 'D' THEN 'LLC Partnership' 
WHEN 'L' THEN 'Corporation ' 
WHEN 'X' THEN 'LLC ' 
WHEN 'R' THEN 'LLC Disregarded Entity' 
WHEN 'O' THEN 'Other' 
WHEN '1' THEN 'Unspecified Business' 
WHEN '2' THEN 'Unspecified Individual' end "TaxIDDesc"

   FROM aptvenD V
LEFT OUTER JOIN CCTCOMPANY CO ON 1=1
where TEN99_CLASS IS NOT NULL and last_year_payments>= :"Min Last Yr Pmnt (eg 600)"