GL account description in AP invoice list

Kinetic ERP
1

Hi everyone,
I’m trying to create the AP invoice report using BAQ.
I want to add GL account description field to this report, so I brought APInvDtl table. But There’s no GL account description field here.
How can i create GL account description field in this AP report?
Thanks in advance!

image
image1931×302 20.3 KB

image
image1850×603 38.1 KB

2

You need to join in the tranGLC table then the GLaccount table to get the description.

Lookup KB0037268 to learn how to join different transaction data to the tranglc.

Hope that helps

3

I joined the tranGLC table with the GLaccount table and then displayed the GL Account field. But These columns are displayed blank. Why is it like that? I looked at case KB0037268, but I can’t figure it out.
Please help.
image

image
image1814×1172 95.2 KB

4

post the baq pls

5

Actually I think I see your problem take a look at this KB KB0028094

It should be related to file = ‘apinvdtl’ and key1 = vendorNum and key2 = InvoiceNum and Key3 = InvoiceLine

This KB KB0039633 gives you more detail… You need to use exprressions in the join to cast the APinvDtl fields to nvarchar.

6

/*

  • Disclaimer!!!
  • This is not a real query being executed, but a simplified version for general vision.
  • Executing it with any other tool may produce a different result.
    */

select
[APInvHed].[InvoiceDate] as [APInvHed_InvoiceDate],
[APInvHed].[DueDate] as [APInvHed_DueDate],
[APInvHed].[InvoiceNum] as [APInvHed_InvoiceNum],
[APInvDtl].[LineType] as [APInvDtl_LineType],
[Vendor].[VendorID] as [Vendor_VendorID],
[Vendor].[Name] as [Vendor_Name],
[APInvDtl].[InvoiceLine] as [APInvDtl_InvoiceLine],
[APInvHed].[CurrencyCode] as [APInvHed_CurrencyCode],
[APInvDtl].[DocExtCost] as [APInvDtl_DocExtCost],
[APInvHed].[DocTaxAmt] as [APInvHed_DocTaxAmt],
(APInvDtl.DocExtCost+ APInvHed.DocTaxAmt) as [Calculated_TotalAmt],
[APInvHed].[InvoiceAmt] as [APInvHed_InvoiceAmt],
[APInvHed].[InvoiceBal] as [APInvHed_InvoiceBal],
[APInvDtl].[PONum] as [APInvDtl_PONum],
[APInvDtl].[POLine] as [APInvDtl_POLine],
[APInvDtl].[PartNum] as [APInvDtl_PartNum],
[APInvDtl].[OurQty] as [APInvDtl_OurQty],
[APInvDtl].[DocUnitCost] as [APInvDtl_DocUnitCost],
[APInvDtl].[ChangedBy] as [APInvDtl_ChangedBy],
[APInvHed].[InvoiceComment] as [APInvHed_InvoiceComment],
[APInvHed].[OpenPayable] as [APInvHed_OpenPayable],
[APInvHed].[DebitMemo] as [APInvHed_DebitMemo],
[APInvHed].[Posted] as [APInvHed_Posted],
[GLAccount].[GLAccount] as [GLAccount_GLAccount],
[GLAccount].[AccountDesc] as [GLAccount_AccountDesc]
from Erp.APInvHed as APInvHed
inner join Erp.APInvDtl as APInvDtl on
APInvHed.Company = APInvDtl.Company
and APInvHed.VendorNum = APInvDtl.VendorNum
and APInvHed.InvoiceNum = APInvDtl.InvoiceNum
left outer join Erp.TranGLC as TranGLC on
APInvDtl.Company = TranGLC.Company
and APInvDtl.VendorNum = TranGLC.VendorNum
and APInvDtl.InvoiceNum = TranGLC.APInvoiceNum
and APInvDtl.InvoiceLine = TranGLC.InvoiceLine
left outer join Erp.GLAccount as GLAccount on
TranGLC.Company = GLAccount.Company
and TranGLC.GLAccount = GLAccount.GLAccount
inner join Erp.Vendor as Vendor on
APInvHed.Company = Vendor.Company
and APInvHed.VendorNum = Vendor.VendorNum
order by APInvHed.Company, APInvHed.InvoiceDate Desc