I was trying to create a Pre Defined Query (PDQ) for an application user. Once the PDQ was ready the user asked to apply sorting on created date to the result. I added the sort criteria and ran the PDQ but to my utter surprise the result was not sorted. I tried doing all sort of changes to the PDQ but to no avail.
When I dug into Siebel tools I saw that the underlying Business Component already had a sort spec defined and that too on created date. I fired up my dedicated client with spool enabled to see what was happening at the DB. The SQL dump showed that when I explicitly applied the sort spec on UI (by clicking on the field label) it worked but it wouldn’t take the default sort spec defined on either BC or PDQ. After researching a bit more I found the reason for it.
The reason for this behavior was All Mode Sort user property had valued FALSE defined at the BC level.
All Mode Sort can have three values and each one of them affects how the sort specifications are applied on the business components. The values of All Mode Sort can be::
- FALSE: Sorting is disabled on the business component
- TRUE: Sorting will be applied on the U1 index of the table. Overrides the sort spec defined at the business component layer
- Normal: Uses business component defined sort spec.
A complete definition and explanation for this user property can also be found in the bookshelf
Unfortunately the only solution in this case was to ask user to apply sorting after running the PDQ as it required an SRF change and structured release to be available to the user.