Oracle R12 Parameter mandatory based on another parameter

Objective:

                Generally we can enable to disable a parameter based on dependent value sets method. There may be scenario where we have to make a parameter mandatory based on another parameter value. For example, in Purchase Order Document Report, we have to make the Release Number parameter mandatory for Blanket PO and non-mandatory for Standard PO.

We need to create three parameters to achieve that. A parameter for PO Number, release number each and one parameter to make the release number mandatory or non-mandatory. In the following screenshot, PO Number, Release Number and Header Id are created for that respective purposes.

          

Value set for P_PO_NUM(PO Number) parameter

Value set for P_HEADER_ID parameter

             
         

The default type of parameter should be set as SQL Statement.

         

The default value query is given below.

select po_header_id from po_headers_all poh where poh.type_lookup_code = 'BLANKET' and segment1 = :$FLEX$.XX_PO_NUMBER

where XX_PO_NUMBER is the value set created for PO Number parameter.

Value set for P_RELEASE_NUM (Release Number) parameter

The release number parameter should be added with NVL clause in the query because it will be passed only for Blanket parameter but it should be made mandatory in Concurrent Program > Parameters.