Solved

Why No Invoice Number In RcvDtl Record?

  • 25 July 2023
  • 1 reply
  • 42 views
M
Userlevel 2

K22.1 Multi-company, multi-plant.

 

The field help on RcvDtl.Invoiced and RcvDtl.InvoiceNum implies that during AP invoicing, these two fields should be loaded coherently with each other.  That is, if a receipt line has Invoiced checked, then there is an InvoiceNum value that gets loaded at the same time.  But in our system, a small number of invoiced receipt lines still have a blank invoice number showing.  In PO Tracker, you can go to the Lines / Invoice Activity and get the invoice numbers that way, and we don’t see anything suspicious about them.

Does anyone know why after AP invoicing, the RcvDtl.InvoiceNum field would be blank?

 

PS. I found KB article KB0104206 which describes the issue, and offers a SQL data fix, but nothing on the root cause.

Thanks,

..Monty.

icon

Best answer by MontyWilson 26 July 2023, 13:46

View original

1 reply

M
Userlevel 2

This is a known issue, documented in this KB article.  However, there is no mention of a fix of the root cause.  It appears to be a simple data fix.  The SQL to find the cases where RcvDtl records are marked as Invoiced but have blank InvoiceNum and zero InvoiceLine fields, and the correct invoice numbers and invoice lines that should be there, is shown below.

 

I am starting work on a data directive for RcvDtl that should prevent future occurrences.


select 

  [RcvDtl].[Company] as [RcvDtl_Company],

  [Vendor].[VendorID] as [Vendor_VendorID],

  [Vendor].[Name] as [Vendor_Name],

  [RcvDtl].[PurPoint] as [RcvDtl_PurPoint],

  [RcvDtl].[PackSlip] as [RcvDtl_PackSlip],

  [RcvDtl].[PackLine] as [RcvDtl_PackLine],

  [RcvDtl].[Invoiced] as [RcvDtl_Invoiced],

  [RcvDtl].[InvoiceNum] as [RcvDtl_InvoiceNum],

  [RcvDtl].[InvoiceLine] as [RcvDtl_InvoiceLine],

  [RcvDtl].[PartNum] as [RcvDtl_PartNum],

  [RcvDtl].[PartDescription] as [RcvDtl_PartDescription],

  [RcvDtl].[OurQty] as [RcvDtl_OurQty],

  [RcvDtl].[IUM] as [RcvDtl_IUM],

  [RcvDtl].[OurUnitCost] as [RcvDtl_OurUnitCost],

  [RcvDtl].[PONum] as [RcvDtl_PONum],

  [RcvDtl].[POLine] as [RcvDtl_POLine],

  [RcvDtl].[PORelNum] as [RcvDtl_PORelNum],

  [RcvDtl].[TranReference] as [RcvDtl_TranReference],

  [RcvHead].[ReceiptDate] as [RcvHead_ReceiptDate],

  [APInvDtl].[InvoiceNum] as [APInvDtl_InvoiceNum],

  [APInvDtl].[InvoiceLine] as [APInvDtl_InvoiceLine]

from Erp.RcvDtl as RcvDtl

inner join Erp.RcvHead as RcvHead on 

  RcvDtl.Company = RcvHead.Company

  and RcvDtl.VendorNum = RcvHead.VendorNum

  and RcvDtl.PurPoint = RcvHead.PurPoint

  and RcvDtl.PackSlip = RcvHead.PackSlip

left outer join Erp.Vendor as Vendor on 

  RcvHead.Company = Vendor.Company

  and RcvHead.VendorNum = Vendor.VendorNum

inner join Erp.APInvDtl as APInvDtl on 

  APInvDtl.Company = RcvDtl.Company

  and APInvDtl.PONum = RcvDtl.PONum

  and APInvDtl.POLine = RcvDtl.POLine

  and APInvDtl.PORelNum = RcvDtl.PORelNum

where (RcvDtl.Invoiced = 1  and RcvDtl.InvoiceNum = '')

Reply


Terms & Conditions