Solved

Create a custom read-only field on the lines of the Purchase order delivery status

  • 21 October 2021
  • 6 replies
  • 334 views
K
Rank
Userlevel 3
Badge +4

 

Hello All,

 I want to create a custom read-only field on the lines of the "Purchase order delivery status" screen (Procurement >  order > Analysis > Purchase order delivery status,  ( see attachement below ) to display the Receipt Note corresponding to each line (content of the Note field of the "Receipt" screen) (see attachement ).

 

 

 

The screen where we have to get the information notes: 

 

 

If there are multiple receptions for the same line, the different notes will be separated by a semi-colon .

 

I have tested a select query but it didn’t work, should i pass by Pl-SQL in this case ? 

 

Thank you.

icon

Best answer by Ruchintha Samararatne 21 October 2021, 11:10

View original
This topic has been closed for comments

6 replies

R
Rank
Userlevel 4
Badge +5

Hi @lagrarif,

 

Will you be able to share the SQL query which you tried to setup the custom field? So that it will make it easier for us to help.

 

/Ruchintha

K
Rank
Userlevel 3
Badge +4

Hi Ruchintha

 

Yes, this is what i tried : “SELECT NOTE_TEXT FROM RECEIPT_INFO WHERE SOURCE_REF1 = :order_no AND SOURCE_REF_TYPE_DB = 'PURCHASE_ORDER'

 

Fatima-ezzahra 

R
Rank
Userlevel 4
Badge +5

Can you try the below query and see whether that is what you expect?

 

select listagg(NOTE_TEXT,'; ') within group(order by source_ref1, source_ref2, source_ref3) notes
  from RECEIPT_INFO
  WHERE source_ref1 = :order_no
    AND SOURCE_REF_TYPE_DB = 'PURCHASE_ORDER'

 

 

R
Rank
Userlevel 4
Badge +5

And also if you want to add the custom field to the line level you could modify the same query as below;

select listagg(NOTE_TEXT,'; ') within group(order by source_ref1, source_ref2, source_ref3) notes
  from RECEIPT_INFO
  WHERE source_ref1 = :order_no
    AND source_ref2 = :line_no
    AND source_ref3 = :release_no
    AND SOURCE_REF_TYPE_DB = 'PURCHASE_ORDER'

 

 

Chamika Dilhani
Rank
Userlevel 6
Badge +7

Hi @lagrarif ,

 

I don’t see an issue with the SELECT statement you have written. I got data for the same.

 

See below.

 

Custom field configuration is as below.

 

 

 

As @Ruchintha Samararatne suggested you can enhance your query which meets your requirement “If there are multiple receptions for the same line, the different notes will be separated by a semi-colon .”

 

 

~Cheers - Chamika
K
Rank
Userlevel 3
Badge +4

Thank you very much @Ruchintha Samararatne , Yes it works with the LISTAGG FUNCTION .

Thank you @Chamika Dilhani :) . 

Terms & ConditionsCookie settings