BAQ to show GL account for unposted APInvoices

Epicor ERP 10
1

I would like to create BAQ where i can show GL account for all the invoices doesn’t matter if posted or not.

Here is my following BAQ i created, i have linked to GLJrnDtl table but it show Gl account only for posted invoices but i would like to see for Unposted invoices as well.

	[Vendor].[GroupCode] as [Vendor_GroupCode],
	[APInvHed].[ApplyDate] as [APInvHed_ApplyDate],
	[VendBank].[BankName] as [VendBank_BankName],
	[Vendor].[VendorID] as [Vendor_VendorID],
	[Vendor].[Name] as [Vendor_Name],
	[APInvHed].[DueDate] as [APInvHed_DueDate],
	[APInvHed].[InvoiceDate] as [APInvHed_InvoiceDate],
	[APInvHed].[InvoiceNum] as [APInvHed_InvoiceNum],
	[APInvHed].[DocInvoiceAmt] as [APInvHed_DocInvoiceAmt],
	[APInvHed].[InvoiceBal] as [APInvHed_InvoiceBal],
	[APInvHed].[InvoiceVendorAmt] as [APInvHed_InvoiceVendorAmt],
	(APInvHed.DocInvoiceAmt-APInvHed.InvoiceAmt) as [Calculated_Variance],
	[GLJrnDtl].[GLAccount] as [GLJrnDtl_GLAccount],
	[APInvHed].[Description] as [APInvHed_Description],
	(CASE APInvHed.OpenPayable WHEN 0 THEN 'FALSE' ELSE 'TRUE' END) as [Calculated_Open],
	[Vendor].[CurrencyCode] as [Vendor_CurrencyCode],
	[Vendor].[PrimaryBankID] as [Vendor_PrimaryBankID]
from Erp.Vendor as Vendor
inner join Erp.APInvHed as APInvHed on 
	Vendor.Company = APInvHed.Company
	and Vendor.VendorNum = APInvHed.VendorNum
	inner join Erp.GLJrnDtl as GLJrnDtl on 
	APInvHed.Company = GLJrnDtl.Company
	and APInvHed.VendorNum = GLJrnDtl.VendorNum
	and APInvHed.InvoiceNum = GLJrnDtl.APInvoiceNum
	and ( not GLJrnDtl.SegValue4 = ''  )

inner join Erp.VendBank as VendBank on 
	Vendor.Company = VendBank.Company
	and Vendor.VendorNum = VendBank.VendorNum```
2

Had a quick look at my DB, and I think it’s held here:

select *
from erp.tranglc
where RelatedToFile = ‘APInvExp’ and Key2 = ‘INVOICE NUM GOES HERE’

3

This could be a tough one because the Posting rule for PUR-??? is to pull the expense account from the Purchase Order ->Part Class ->GL Control. and if that is not available go to the company setting.
Look at the COSandWIP posting rule to determine the logic that you will need to pull in.