Hmmm... pretty sure I know who's posting this and I thought we solved the problem?
The solution discussed was to use a QBDE (based on a SQL Data View) to calculate the days until the 3rd Monday of the month. Was that not working?
I am not sure if it was discussed earlier. Need to check..
QBED can only be used with numeric type, how i can calculate the days
You can calculate the number of days until the 3rd Monday... needs to be done in SQL. Please email me if you need more info.
We have a similar issue and we thought of using this approach but I'm not quite sure how to link my QBDE to the Data view.
These are the steps taken
1. Have Data view in place and its published.
2. Added QBDE on the BP form.
3. Under BP configuration/ query tab/data source from here, it does not show up the date view
Are we missing a step?
Thanks in advance
Your query in Data View must contain project_id , then only it will show up in configuration.
You could also try having a BP record (maybe at company level) that has a field that holds the 3rd Monday date field for every month for the next several years.User selects a (filtered?) record and auto-populates the 3rd Monday date. If that date matches creation date, then the record can route.
Seems odd though, because if I create today and it is not the 3rd Monday, how will creation date ever match 3rd Monday, since creation date is determined after successfully routing to the the 2nd step?
Thanks for the suggestion!
I achieved this through a QBDE (Calculating 3rd monday date from a SQL query).
Could you post the SQL here? That would be pretty useful for some other things.
Below is the query returning 3 monday date. Added with case returning (0 if sysdate is 3rd monday..) based on the requirement.
select TRUNC(NEXT_DAY(SYSDATE - to_char(SYSDATE,'dd')+1,'Monday')+14) as date_checker1 from dual
Autocreating using Date trigger.
Where do you enter the SQL Query?