Forum Stats

  • 3,733,852 Users
  • 2,246,830 Discussions


How to add UNION in Valuset

Bommi Member Posts: 703 Bronze Badge

Hi Team,

We are using R12.2.3

Is there any way to write UNION in valueset WHERE Clause?

We have a requirement on JE Line DFF as:

  1. Line DFF is with 3 segments Vendor Name, PO# and Invoice#
  2. PO# is dependent on Vendor Name(Which means system has to show only POs under that Vendor)
    1. Here, entering PO# is not mandatory
  3. Invoice# is dependent on PO#. Here,
    1. If user provided PO# then system has to show only Invoices under that PO
    2. If no PO# is given, then system has to show all the Invoices under that Vendor

So, I created a valueset XX_VENDOR_NAME_VS for vendor names and XX_PO_NUMBER_VS for PO Numbers. Now, the issue comes on how to create XX_INVOICE_NUMBER_VS as PO# is not mandatory.

Can anyone please help me on this.

Thanks in Advance,



  • Bommi
    Bommi Member Posts: 703 Bronze Badge

    Hi All,

    I achieve this through an alternate way as below.

    On XX_PO_NUMBERS_VS valuset, the table name is mentioned as

    (select poh.segment1,poh.enabled_flag,poh.currency_code,pov.vendor_name from po_headers_all poh,po_vendors pov where pov.vendor_id=poh.vendor_id UNION SELECT 'N/A','Y',NULL,NULL FROM DUAL)po

    Now, under WHERE clause, I mentioned as:

    WHERE 1=1

    AND (po.vendor_name IS NULL or po.vendor_name=:$FLEX$.XXNPI_GL_Journal_Vendor_Name)

    ORDER BY po.segment1

    Now, the WHERE Clause of XX_INVOICE_NUMBER_VS is:

    WHERE 1=1

    AND api.vendor_id=pov.vendor_id

    AND api.quick_po_header_id=poh.po_header_id(+)

    and pov.vendor_name=:$FLEX$.XX_Vendor_Name_VS


    This means, users has to select atleast N/A under PO#. If N/A is selected, then system is showing all the invoices. If any particular PO# is selected, then system is showing only Invoices under that PO as below

    But, still is there any other way to let the users select Invoice# without even entering N/A for PO#?



Sign In or Register to comment.