Forum Stats

  • 3,733,852 Users
  • 2,246,830 Discussions
  • 7,856,896 Comments

Discussions

How to add UNION in Valuset

Bommi
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

Answers

  • 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

    and DECODE(:$FLEX$.XX_PO_NUMBER_VS,'N/A',1,poh.segment1)=DECODE(:$FLEX$.XX_PO_NUMBER_VS,'N/A',1,:$FLEX$.XX_PO_NUMBER_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#?

    Thanks,

    Srivathsava

Sign In or Register to comment.