Register today for Oracle CloudWorld. October 17th - 20th, Las Vegas

Register now

Hillel Cooperman, Senior VP of User Experience Design, has a message for you on Oracle CloudWorld

Watch now
Need a query for a date parameter dependent to another date parameter. — Cloud Customer Connect
You're almost there! Please answer a few more questions for access to the Applications content. Complete registration
Interested in joining? Complete your registration by providing Areas of Interest here. Register

Need a query for a date parameter dependent to another date parameter.

Accepted answer
23
Views
1
Comments
edited May 19, 2021 5:39PM in Reporting and Analytics for SCM 1 comment

Content

Hello all,

There's a query I need to get the month names separately with one parameter and another parameter to select a date range. The month name parameter should be dependent on the data range. Below mentioned is a part of my main query.

------------------------------------------------------ month name parameter 
    AND ( TO_CHAR(
        DFLA.ACTUAL_SHIP_DATE,
        'MONTH'
    ) IN ( :PMONTH )
          OR 'All' IN ( :PMONTH || 'All' ) )

------------------------------------------------------ date range parameter 
    AND TO_CHAR(
        DFLA.ACTUAL_SHIP_DATE,
        'YYYY-MM-DD'
    ) BETWEEN TO_CHAR(
        :PFROMDATE,
        'YYYY-MM-DD'
    ) AND TO_CHAR(
        :PTODATE,
        'YYYY-MM-DD'
    )

 

For the month name parameter, I have created a LOV,

 

SELECT
    TO_CHAR(
        ADD_MONTHS(

Howdy, Stranger!

Log In

To view full details, sign in.

Register

Don't have an account? Click here to get started!