BAQ for AP Received but Not Invoiced Report

Epicor ERP 10
1

Does anyone have any advice on creating a BAQ that would recreate the data from the AP Received but Not Invoiced Report? I can get it pretty close, but it ends up only giving me PO numbers that HAVE invoices (so doing the opposite of what I would like).

2

Change your join of the two tables to include all of PO’s. Then you will get a list, and you can filter by the rows that don’t have a invoice attached (null)

image.png897×477 24.3 KB

3

Mine looks different from that. The only join options I have are Inner, Left, Right and Full. Would this be a version difference?

4

Out of curiosity, why use AP invoice Table? If you’re looking for Received Not Invoiced Items then:
Use RcvDtl table and under Table criteria use “Invoiced = False”.

You could get all the fields you want after that and will only show items that has NOT been invoiced yet.

If you drop-ship then those items not yet invoiced will NOT be on this table. you would have to do the same to “DropShipDtl” table and make “APInvoice = False”

5

Yup, that changed from 10.0 to 10.1. (big issue in the nerd world I heard) It’s the same thing. Look at the symbol on the join to help determine. the squared brackets mean all of that table.

6

Quick update. I haven’t gotten much time to play around with it yet, but based on some quick testing I have a BAQ now that matches to my Received Not Invoiced Report. Here it is:

select
[RcvDtl].[PONum] as [RcvDtl_PONum],
[RcvDtl].[POLine] as [RcvDtl_POLine],
[RcvDtl].[PackSlip] as [RcvDtl_PackSlip],
[Vendor].[Name] as [Vendor_Name],
[RcvDtl].[Received] as [RcvDtl_Received],
[RcvDtl].[Invoiced] as [RcvDtl_Invoiced],
[PODetail].[VoidLine] as [PODetail_VoidLine],
[RcvDtl].[OurQty] as [RcvDtl_OurQty],
[RcvDtl].[OurUnitCost] as [RcvDtl_OurUnitCost],
(RcvDtl.OurQty * RcvDtl.OurUnitCost) as [Calculated_ExtCost]
from Erp.RcvDtl as RcvDtl
inner join Erp.PODetail as PODetail on
RcvDtl.Company = PODetail.Company
And
RcvDtl.PONum = PODetail.PONUM
And
RcvDtl.POLine = PODetail.POLine
and ( PODetail.VoidLine = false )

inner join Erp.Vendor as Vendor on
RcvDtl.Company = Vendor.Company
And
RcvDtl.VendorNum = Vendor.VendorNum

where (RcvDtl.Invoiced = false and RcvDtl.Received = true)

Thank you all for your help!

3 Likes
7

I have added the Receipt Date filter in this query

SELECT
[RcvDtl].[PONum] as [RcvDtl_PONum],
[RcvDtl].[POLine] as [RcvDtl_POLine],
[RcvDtl].[PackSlip] as [RcvDtl_PackSlip],
[Vendor].[Name] as [Vendor_Name],
[RcvDtl].[Received] as [RcvDtl_Received],
[RcvDtl].[Invoiced] as [RcvDtl_Invoiced],
[PODetail].[VoidLine] as [PODetail_VoidLine],
[RcvDtl].[OurQty] as [RcvDtl_OurQty],
[RcvDtl].[OurUnitCost] as [RcvDtl_OurUnitCost],
(RcvDtl.OurQty * RcvDtl.OurUnitCost) as [Calculated_ExtCost]
FROM Erp.RcvDtl as RcvDtl
INNER JOIN Erp.PODetail as PODetail on RcvDtl.Company = PODetail.Company And RcvDtl.PONum = PODetail.PONUM And RcvDtl.POLine = PODetail.POLine and (PODetail.VoidLine = 0 )
INNER JOIN Erp.Vendor as Vendor on RcvDtl.Company = Vendor.Company And RcvDtl.VendorNum = Vendor.VendorNum
WHERE (RcvDtl.Invoiced = 0 and RcvDtl.Received = 1) AND RcvDtl.ReceiptDate between ‘2023-01-01’ and ‘2023-12-31’
ORDER BY 1