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

Hi @Thomas Peterson 

Can you comment here please? Did I miss something?

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, 

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,

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 

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.

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.

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