-- Drop stored procedure if it already exists
IF object_id(N'ddv_PayablesWithPOs', 'V') IS NOT NULL
DROP VIEW ddv_PayablesWithPOs
GO
CREATE view ddv_PayablesWithPOs
AS
-- select * from ddv_PayablesWithPOs order by InvoiceNumber
select
g5.actnumst as GLAccount,
v.VENDNAME as VendorName,
pt.VCHRNMBR as InvoiceNumber,
pt.DOCAMNT as Amount,
dist.DEBITAMT,
PT.DOCDATE as InvoiceDate,
'' as Comments,
dbo.ddf_phone(v.PHNUMBR1) as Telephone,
dbo.ddf_Address(null,null, v.address1, v.address2, v.address3, v.city, v.state, v.zipcode) as Address,
pt.VENDORID as VendorID,
pt.DOCNUMBR,
dbo.ddf_CommaSeparatedReceipts(pt.VENDORID, pt.DOCNUMBR) as Release,
dbo.ddf_CommaSeparatedPOs(pt.VENDORID, pt.DOCNUMBR) as PONumber,
pt.docnumbr as VendorDocument,
case when pt.CURTRXAM = 0 then 'Yes' else 'No' end as Paid,
'OPEN' AS Source
from PM20000 pt
join pm00200 v on v.vendorid = pt.vendorid
left join pm10100 dist on dist.VCHRNMBR = pt.VCHRNMBR and dist.debitamt > 0
left join gl00105 g5 on g5.actindx = dist.dstindx
where pt.DOCTYPE = 1
union all
select
g5.actnumst as GLAccount,
v.VENDNAME as VendorName,
pt.VCHRNMBR as InvoiceNumber,
pt.DOCAMNT as Amount,
dist.DEBITAMT,
PT.DOCDATE as InvoiceDate,
'' as Comments,
dbo.ddf_phone(v.PHNUMBR1) as Telephone,
dbo.ddf_Address(null,null, v.address1, v.address2, v.address3, v.city, v.state, v.zipcode) as Address,
pt.VENDORID as VendorID,
pt.DOCNUMBR,
dbo.ddf_CommaSeparatedReceipts(pt.VENDORID, pt.DOCNUMBR) as Release,
dbo.ddf_CommaSeparatedPOs(pt.VENDORID, pt.DOCNUMBR) as PONumber,
pt.docnumbr as VendorDocument,
case when pt.CURTRXAM = 0 then 'Yes' else 'No' end as Paid,
'HISTORY' AS Source
from PM30200 pt
join pm00200 v on v.vendorid = pt.vendorid
left join pm30600 dist on dist.VCHRNMBR = pt.VCHRNMBR and dist.DOCTYPE = pt.doctype and dist.debitamt > 0
left join gl00105 g5 on g5.actindx = dist.dstindx
where pt.DOCTYPE = 1
-- select * from pm10100
GO
grant select on ddv_PayablesWithPOs to public