Question

GL Voucher Rows and Invoice Line Connection

  • 9 February 2023
  • 8 replies
  • 406 views
MishaelAkleker
Rank
Userlevel 3
Badge +4

Hello, 

Thank you for taking the time to read :) 

Do we have any way of knowing the connection between the Voucher, the GL Voucher Rows and the customer invoice line. 

For example, on one specific invoice, we may have multiple lines with different part numbers, prices and quantities which all translate to one posting per line in the general ledger. However we would like to find the link between these individual lines on the customer invoice and the individually posted lines on the GL voucher Rows screen. 

The only link we can find at the moment is between the invoice number and GL voucher rows, but not per line on the invoice (just using the header) 

For instance: 

The customer invoice below has two lines (as you can see there is POS 1 and POS 2 to represent each line) 

 

Looking into the GL 

We can see that there are two corresponding lines but they not have the reference for POS 1 and 2 as we saw above. Is there any way to connect the two lines in the invoice above to the the voucher rows posting below (common connector). 

 

Mish

8 replies

F
Rank
Userlevel 6
Badge +19

Hello @MishaelAkleker 

I have checked CUST_ORDER_INV_ITEM_UIV_ALL, OUT_INV_UTIL_VOU_QRY and GEN_LED_VOUCHER_ROW_UNION_QRY but there is no field specifying item id from invoice rows.

Good luck with it

https://superp.solutions/
F
Rank
Userlevel 6
Badge +19

Hi @Thomas Peterson 

Can you comment here please? Did I miss something?

https://superp.solutions/
T
Rank
Userlevel 7
Badge +18

Hi, 

Yes, a connection exists but it’s a bit interesting.   I was able to link GL back through different views.

Every invoice has lines.  These lines are then connected to invoice postings.   If I remember correctly you need to join the “internal” invoice number with the invoice lines and postings.  To see the links, locate an invoice with multiple lines and view the data in some sql tool.     You will need to review several views to find the best for linking the data. 

It’s not clean / easy for most users.    

My question then to “my” clients with similar request is - what’s the real need for a given report?  Can we show customer invoice and the postings? - Even connect that to the customer order.    That’s far easier.     

Sorry, but I have a deadline.  I can’t look into my notes right now.

 

Best regards, 

J
Rank
Badge

Thanks for the replies so far on this (colleague of Mish here). 

I’ve been looking at the following views, but so far I’m still unable to locate any sort of link between the GL Voucher Row and the Invoice Line:

Invoice Posting Analysis -  OUTGOING_INVOICE2 and INV_ACCOUNTING_ROW2
Customer Invoice Analysis - OUTGOING_INVOICE_QRY and INVOICE_ITEM_CUST
OUT_INV_UTIL_VOU_QRY

One part I’m also not clear on is the Internal Invoice number, so perhaps that’s the part I’m missing. 

Any further guidance would be much appreciated.

Kind regards,

T
Rank
Userlevel 7
Badge +18

Hi, 

The invoice number is what I would describe as an external number - shown on reports and viewable by users.   The invoice Id attribute / value is what I would describe as an internal number meaning users would not see that value, but IFS uses this to tie the records together. The most useful views for this inquiry used the invoice id attribute and other such as item_id (or maybe it was Item_no) I’m going off memory. 

In a view I’ve used previously, the invoice postings noted the invoice id and an item id (like 1,2,3,4 etc). 

The GL voucher rows 1,2,3,4 matched the attribute values I had found in a view that listed the postings rows.  

I don’t have quick access to my previous notes (at this time). 

Best regards 

J
Rank
Badge

Thanks for the direction @Thomas Peterson 

I believe that I’ve been able to track down most of the info, at least so far as being able to get a solution working for now.

I have linked

GEN_LED_VOUCHER_ROW_UNION_QRY GL
(Note that we have a Custom Field on this view for Invoice_No)

to the following views:

CUST_ORDER_INV_ITEM_UIV_ALL COII
LEFT JOIN INV_ACCOUNTING_ROW IAR
  ON IAR.invoice_id = COII.invoice_id
  and IAR.item_id = COII.item_id
LEFT JOIN CUSTOMER_ORDER_INV_HEAD  COIH
  ON COIH.INVOICE_ID = COII.INVOICE_ID    
WHERE COIH.INVOICE_NO = GL.CF$_INVOICE_NO
  AND IAR.curr_amount= (GL.CURRENCY_AMOUNT*-1)
  AND COII.invoiced_qty = GL.QUANTITY 

and in order to link the values, I had to link the GL data using the Quantity and Currency Amount. 

The view INV_ACCOUNTING_ROW matches the Invoice ID to the Item ID (row) of the Customer Invoice, and has records matching the GL Voucher Row Currency_Amount breakdowns. 

I still wasn’t able to find a view which matched the GL Voucher Row Row_No field to the exact Customer Invoice Item_ID. If anyone knows of a view that does this, I’d be interested to hear back from you.

T
Rank
Userlevel 7
Badge +19

There is no item_id field on the voucher row in general ledger because one line in general ledger voucher row can have several invoice posting lines as a source.

When creating vouchers from an invoice the postings on the invoice is grouped per code string + some other fields.

E
Rank
Badge +2

Hi all,

I’m working on a similar issue (tying a part number to a voucher line). If I can get to the invoice line, that would work. Has any new information been discovered? I need to find a solution.

Thanks,

EDM

Reply


Terms & ConditionsCookie settings