@kacperifs as far as I know the only reference that you could use between GL and Supplier would be the IDENTITY from GL Voucher Rows. The identity field is populated with either Customer No or Supplier No depending on what kind of transaction it is.

So I imagine you could do something like:

• gl_vou_rows.identity = supplier_info.supplier_id

And then based on this relation, get the text for GL Voucher Header.

Hi @Marcel.Ausan 

First for all, thank you for your reply. You want to suggest me that to use this reference as LEFT JOIN?

• gl_vou_rows.identity = supplier_info.supplier_id

I dont see column Identity or Supplier Id at gen_led_voucher_row table. It only exists at ledger_item_su_qry. Once again:

 

As you can see querry works. When I want to add this as custom:

 

Also I turned on additional view:

 

And it still doesn’t work.

Hi @kacperifs,

Could you share how you specify the SELECT statement when creating the Custom Attribute? 

Hi @Charith Epitawatta 

SELECT DISTINCT glv.TEXT AS TEKST
FROM GEN_LED_VOUCHER_ROW glv 
INNER JOIN LEDGER_ITEM li ON glv.company = li.company
INNER JOIN LEDGER_ITEM_SU_QRY lis ON glv.company = lis.company

WHERE glv.reference_number = lis.ledger_item_id
  AND glv.account = lis.CODE_A
  AND glv.party_type_id =: identity
  AND lis.voucher_no =: voucher_no
  


  

@kacperifs  if the entity that you are querying is LedgerItem then I guess the below query should work. I’m wondering if there should be an additional column added to the join between glv and li, but I guess it should work.

SELECT glv.text
from GEN_LED_VOUCHER_ROW  glv, LEDGER_ITEM_SU_QRY li
where 1=1
and glv.company = li.company
and li.party_type_db = 'SUPPLIER'
and glv.voucher_type = li.voucher_type
and glv.voucher_no = li.voucher_no
and li.ledger_item_series_id = :ledger_item_series_id
and li.ledger_item_id = :ledger_item_id
and li.ledger_item_version = :ledger_item_version

Hi @Marcel.Ausan 

Thank you for your reply. As i tried to implement your querry, it still shows me an error:

@kacperifs since it’s trying to find voucher_no in Ledger_Item table, I guess you put voucher_no as an argument which is wrong. Your arguments should be v.ledger_item_series_id, v.ledger_item_id, v.ledger_item_version

I copy/pasted the code I provided above and for me it works:

@Marcel.Ausan I had to turn off additional view before i added this querry, thanks a lot. For now i have trouble with adding this column via page designer:

 

Supplier Analysis - > Ledger Item

@Marcel.Ausan Nevermind. It all worked as you provided to me. The problem was again with additional views. I had to set them like at the screenshot below:

 

Anyway, thanks a lot for your help, I appreciate it.

@Marcel.Ausan 

Once again, is there a any reference between LEDGER_ITEM and POSTING_PROPOSAL_COST_AUTH? I need also to pick TEXT from POSTING_PROPOSAL_COST_AUTH. When I use Invoice_ID and Company it shows me error: OBJECT is not REF. 

Hi, 

SELECT LISTAGG(pp.TEXT, '; ') AS TEXT
FROM Posting_Proposal pp
JOIN Ledger_Item li ON pp.Invoice_Id = li.Invoice_Id
WHERE 1=1
AND pp.Invoice_Id = :Invoice_Id
AND pp.company = li.company

If anyone will ever need that, i paste my querry which worked for me.