table relation between PO receipt <-> invoice <-> ap

(0) Share

Report

Posted on by Community Member

What is the relation between PO receipt <-> invoice <-> AP ??

For example,

We would like to know this item invoice in VendInvoiceTrans table is created for which PO receipt in VendPackingSlipTrans table.

Moreover, we would like to know this AP voucher in ledgerjournaltrans table is created for which invoice in VendInvoiceTrans table.

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (0)

Mariano Gracia on at

Like (0)

Report

RE: table relation between PO receipt <-> invoice <-> ap

If you use stocked products, you can find this information in the inventory transactions, there you can find invoice and packing slip number, and using the ledger button, you can view the ledger:

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: table relation between PO receipt <-> invoice <-> ap

If our item is service, after the posting is done, is there any link between receipt and invoice ???

Was this reply helpful? Yes No
Suggested answer

Brandon Wiese 17,788 on at

Like (0)

Report

RE: table relation between PO receipt <-> invoice <-> ap

Check out the table named VendInvoicePackingSlipQuantityMatch.

Was this reply helpful? Yes No
Suggested answer

makdax2012r3 50 on at

Like (0)

Report

RE: table relation between PO receipt <-> invoice <-> ap

you can use this query for matching quantity.

select pt.PURCHID 'po num',pt.OrderAccount 'vendor code',pt.PURCHNAME'Vendor Name' ,pl.LINENUMBER ,pl.ITEMID,pl.NAME,pl.PURCHQTY,
vpst.PACKINGSLIPID, vpst.QTY 'PACKINGSLIP recv qaty',vinvt.INVOICEID,vinvt.QTY 'inv Qty',vinvt.LINEAMOUNT
from PURCHTABLE pt
inner join PURCHLINE pl on pt.PURCHID = pl.PURCHID and pt.DATAAREAID = pl.DATAAREAID
left join VENDPACKINGSLIPTRANS vpst on vpst.INVENTTRANSID = pl.INVENTTRANSID and vpst.DATAAREAID = pt.DATAAREAID
left join VendInvoicePackingSlipQuantityMatch Vinvpackqm on Vinvpackqm.PACKINGSLIPSOURCEDOCUMENTLINE =vpst.SOURCEDOCUMENTLINE --and Vinvpackqm.DATAAREAID = pt.DATAAREAID
left join VENDINVOICETRANS vinvt on vinvt.SOURCEDOCUMENTLINE = Vinvpackqm.INVOICESOURCEDOCUMENTLINE
where pt.DATAAREAID='USMF' and pt.PURCHSTATUS <>'4' and pt.DOCUMENTSTATE <>'0' and Pt.PURCHID='000021' and vpst.QTY<>0

------------------------------------ and second query -----

select pt.PURCHID 'po num',pt.OrderAccount 'vendor code',pt.PURCHNAME'Vendor Name' ,pl.LINENUMBER ,pl.ITEMID,pl.NAME,pl.PURCHQTY,
vpst.PACKINGSLIPID, vpst.QTY 'PACKINGSLIP recv qaty',vinvt.INVOICEID,vinvt.QTY 'inv Qty',vinvt.LINEAMOUNT
from PURCHTABLE pt
inner join PURCHLINE pl on pt.PURCHID = pl.PURCHID and pt.DATAAREAID = pl.DATAAREAID
left join VENDPACKINGSLIPTRANS vpst on vpst.INVENTTRANSID = pl.INVENTTRANSID and vpst.DATAAREAID = pt.DATAAREAID --and vpst.ITEMID = pl.ITEMID --and vpst.VENDPACKINGSLIPJOUR= vpsj.RECID and vpst.DATAAREAID = pt.DATAAREAID
left join VENDINVOICETRANS vinvt on vinvt.INVENTTRANSID =pl.INVENTTRANSID and vinvt.DATAAREAID = pt.DATAAREAID -- vinvt.RECID = vpst.InvoiceTransRefRecId

where pt.DATAAREAID='USMF' and pt.PURCHSTATUS <>'4' and pt.DOCUMENTSTATE <>'0' and Pt.PURCHID='000021' and vpst.QTY<>0

Was this reply helpful? Yes No
makdax2012r3 50 on at

Like (0)

Report

RE: table relation between PO receipt <-> invoice <-> ap

you can use these relations

left join VENDPACKINGSLIPTRANS vpst on vpst.INVENTTRANSID = pl.INVENTTRANSID and vpst.DATAAREAID = pt.DATAAREAID
left join VendInvoicePackingSlipQuantityMatch Vinvpackqm on Vinvpackqm.PACKINGSLIPSOURCEDOCUMENTLINE =vpst.SOURCEDOCUMENTLINE --and Vinvpackqm.DATAAREAID = pt.DATAAREAID
left join VENDINVOICETRANS vinvt on vinvt.SOURCEDOCUMENTLINE = Vinvpackqm.INVOICESOURCEDOCUMENTLINE

------------------------------------------------------------------------- and also used ---------------------------------------------------------------------------------

inner join PURCHLINE pl on pt.PURCHID = pl.PURCHID and pt.DATAAREAID = pl.DATAAREAID
left join VENDPACKINGSLIPTRANS vpst on vpst.INVENTTRANSID = pl.INVENTTRANSID and vpst.DATAAREAID = pt.DATAAREAID --and vpst.ITEMID = pl.ITEMID --and vpst.VENDPACKINGSLIPJOUR= vpsj.RECID and vpst.DATAAREAID = pt.DATAAREAID
left join VENDINVOICETRANS vinvt on vinvt.INVENTTRANSID =pl.INVENTTRANSID and vinvt.DATAAREAID = pt.DATAAREAID --

Was this reply helpful? Yes No