I think Victoria Yudin has created the view you need to accomplish this. If you have a one to one relationship between vendor invoices and the check that pays them you'll get one line per transaction. If not, you'll get multiple lines. Victoria is exceptional and it will benefit you to look at the other views she has created and shares with us:
victoriayudin.com/.../payables-sql-views
Here's the view Victoria Created for apply information:
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- view_AP_Apply
-- Created on Oct 10 2008 by Victoria Yudin, Flexible Solutions
-- For updates see victoriayudin.com/gp-reports
-- Does not take Multicurrency into account
-- Will return multiple lines for transactions applied to by
-- more than one check, credit memo or return
-- Updated Jan 6 2009 to include Vendor Name
-- Updated Feb 20 2009 to include trx not fully applied
-- Updated Mar 20 2009 to include Payment Date
-- Updated Mar 30 2009 to include Apply Date
-- Updated Apr 28 2009 to include Apply GL Posting Date
-- Updated May 27 2009 to add Payment Status, take space
-- out of column names and eliminate duplicates for
-- payment that are not fully applied
-- Updated Jan 29 2014 to add 1099 columns
-- Updated Apr 10, 2015 to use APPLDAMT instead of APFRMAPLYAMT
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
create view view_AP_Apply
as
select
P.VENDORID Vendor_ID,
PM.VENDNAME Vendor_Name,
case P.DOCTYPE
when 1 then 'Invoice'
when 2 then 'Finance Charge'
when 3 then 'Misc Charge'
end Document_Type,
P.DOCDATE Document_Date,
P.VCHRNMBR Voucher_Number,
P.DOCNUMBR Document_Number,
P.DOCAMNT Document_Amount,
P.TEN99AMNT [1099_Amount],
P.TEN99BOXNUMBER [1099_Box],
case P.TEN99TYPE
when 1 then 'Not a 1099 Vendor'
when 2 then 'Divident'
when 3 then 'Interest'
when 4 then 'Miscellaneous'
when 5 then 'Withholding'
else ''
end [1099_Type],
coalesce(PA.APPLDAMT,0) Applied_Amount,
coalesce(PA.VCHRNMBR,'') Payment_Voucher_Number,
coalesce(P2.DOCNUMBR,'') Payment_Doc_Number,
coalesce(P2.DOCDATE,'1/1/1900') Payment_Date,
coalesce(PA.DATE1,'1/1/1900') Apply_Date,
coalesce(PA.GLPOSTDT, '1/1/1900') Apply_GL_Posting_Date,
case PA.DOCTYPE
when 4 then 'Return'
when 5 then 'Credit Memo'
when 6 then 'Payment'
else ''
end Payment_Type,
coalesce(PA.POSTED, 'Unpaid') Payment_Status
from
(select VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR,
DOCAMNT, VOIDED, TEN99AMNT, TEN99BOXNUMBER, TEN99TYPE
from PM30200
union all
select VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR,
DOCAMNT, VOIDED, TEN99AMNT, TEN99BOXNUMBER, TEN99TYPE
from PM20000) P
left outer join
(select VENDORID, APTVCHNM, APTODCTY, APFRDCNM, DOCTYPE,
DOCDATE, APPLDAMT, VCHRNMBR, DATE1, GLPOSTDT,
case POSTED when 0 then 'Unposted' else 'Posted' end POSTED
from PM10200
union
select VENDORID, APTVCHNM, APTODCTY, APFRDCNM, DOCTYPE,
DOCDATE, APPLDAMT, VCHRNMBR, DATE1, GLPOSTDT,
'Posted' POSTED
from PM30300) PA
on P.VCHRNMBR = PA.APTVCHNM and P.VENDORID = PA.VENDORID
and P.DOCTYPE = PA.APTODCTY
left outer join
(select VCHRNMBR, DOCTYPE, DOCNUMBR, DOCDATE
from PM20000
union all
select VCHRNMBR, DOCTYPE, DOCNUMBR, DOCDATE
from PM30200) P2
on P2.VCHRNMBR = PA.VCHRNMBR and P2.DOCTYPE = PA.DOCTYPE
left outer join PM00200 PM
on P.VENDORID = PM.VENDORID
where P.DOCTYPE in (1,2,3) and P.VOIDED = 0
-- add permissions for DYNGRP
go
grant select on view_AP_Apply to DYNGRP
Share
Report
All responses (
Answers (
65,271
