Solved

Custom field - is there any reference? IFS CLOUD

  • 16 January 2024
  • 11 replies
  • 163 views
K
Rank
Badge +6

Hi all,

I have question if it is possible to add column “TEXT” from GL Voucher Row Analysis to Supplier Analysis? I have created SQL which works in ORACLE SQL and return me a propper data but when i want to add it as custom field system shows me error that there is somekind of issue with my reference. I wanted to connect it by voucher_no. 

Also when I was checking it by querries creator it showed me that there is no references between those projections. 

I am using IFS CLOUD.

icon

Best answer by Marcel.Ausan 17 January 2024, 12:35

View original

11 replies

Marcel.Ausan
Rank
Userlevel 6
Badge +15

@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.

In my example identity is a customer no since it’s coming from M28 transaction code.

 

https://www.linkedin.com/in/marcelausan/
K
Rank
Badge +6

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.

Charith Epitawatta
Rank
Userlevel 7
Badge +31

Hi @kacperifs,

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

Charith - https://charith.xyz
K
Rank
Badge +6

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
  


  

Marcel.Ausan
Rank
Userlevel 6
Badge +15

@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

 

https://www.linkedin.com/in/marcelausan/
K
Rank
Badge +6

Hi @Marcel.Ausan 

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

 

 

Marcel.Ausan
Rank
Userlevel 6
Badge +15

@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:

 

 

https://www.linkedin.com/in/marcelausan/
K
Rank
Badge +6

@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

K
Rank
Badge +6

@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.

K
Rank
Badge +6

@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. 

K
Rank
Badge +6

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. 

Reply


Terms & ConditionsCookie settings