Adaptive Search Queries: Filtering Dates By Fiscal Quarter

Adaptive Search supports filtering based on fiscal quarters for different years and fiscal codes supported by the Sales and Fusion Service Calendar. This lets you send in fiscal specifics and build aggregations based on date ranges. The calendar are supported by all Fusion applications and are managed using the Manage Accounting Calendars Setup task. This is controlled using Profile Option ZCA_COMMON_CALENDAR.

For example, you can pass a quarter number for a specific year to build buckets upon.

The header information is as below:

POST {{url}}/crmRestApi/searchResources/11.13.18.05/custom-actions/queries?
HTTP/1.1
Host: servername.fa.us2.oraclecloud.com
Authorization: Basic c2FsZXNfYWRtaW46V2VsY29tZTE=
Content-Type: application/json
Preference: transient

The request payload is as below:

{   
    "entity":"Account",
    "limit":1,
    "fields":["PartyUniqueName","CreationDate"],
    "aggregations":{
        "CreationDate":{
            "ignore":true,
            "range":{
                "attribute":"CreationDate",
                "localize":"true",
                "ranges":[
                    {"key": "FiscalQuarter_+00", "from": "now/fq", "to": "now/fq+1fq"},
                    {"key": "FiscalQuarter_+01", "from": "now/fq+1fq", "to": "now/fq+2fq"},
                    {"key": "FiscalQuarter_+02", "from": "now/fq+2fq", "to": "now/fq+3fq"},
                    {"key": "FiscalQuarter_+03", "from": "now/fq+3fq", "to": "now/fq+4fq"},
                    {"key": "FiscalQuarter_+04", "from": "now/fq+4fq", "to": "now/fq+5fq"}
                ] 
            }
        }
    }
}
 

The response for a successful call is as below:

{
    "items": [
        {
            "PartyId": 300100169111795,
            "PartyUniqueName": "Apple (Cupertino, US)",
            "CreationDate": "2018-12-08T11:56:31.910+0000",
            "_entity": "Account",
            "links": [
                {
                    "rel": "canonical",
                    "href": "http://servername.fa.us2.oraclecloud.com:9011/crmRestApi/searchResources/11.13.18.05/entities/Account/300100169111795?onlyData=false=US",
                    "kind": "item",
                    "name": "canonical"
                }
            ]
        }
    ],
    "aggregations": [
        {
            "CreationDate": {
                "buckets": [
                    {
                        "key": "FiscalQuarter_+00",
                        "count": 0,
                        "localizedKey":"Q1FY17"
                    },
                    {
                        "key": "FiscalQuarter_+01",
                        "count": 509,
                        "localizedKey":"Q2FY17"
                    },
                    {
                        "key": "FiscalQuarter_+02",
                        "count": 0,
                        "localizedKey":"Q3FY17"
                    },
                    {
                        "key": "FiscalQuarter_+03",
                        "count": 4,
                        "localizedKey":"Q4FY17"
                    },
                    {
                        "key": "FiscalQuarter_+04",
                        "count": 0,
                        "localizedKey":"Q1FY18"
                    }
                ]
            }
        }
    ],
    "count": 1,
    "hasMore": true,
    "limit": 1,
    "offset": 0,
    "totalResults": 509
}