WikiAdmin: 1 revision imported

2023-07-14T12:58:47Z

1 revision imported

← Older revision

Revision as of 12:58, 14 July 2023

Chanson at 20:41, 28 January 2019

2019-01-28T20:41:43Z

New page

:[[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.

<PRE>
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
</PRE>

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==

<pre>
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)"
</pre>

Accounts Payable Report Queries - Revision history

WikiAdmin: 1 revision imported

Chanson at 20:41, 28 January 2019