opsCTRL Supplier Developer API – opsCTRL opsCTRL Supplier Developer API – opsCTRL

API Reports

Opportunity Reports

This report retrieves a list of all your entities opportunities.

Report Format

Http Request Format: POST
Report URL: https://191wsd17nc.execute-api.eu-west-1.amazonaws.com/production/report
Reports are returned in a JSON format.

 Power Query Structure & Functions

Functions Used: 
Table.FromList – PowerQuery M | Microsoft Docs (Used to convert JSON data into a table format)

Splitter functions – PowerQuery M | Microsoft Docs (Identifies data is not delimited by comma or other delimeter)

 

let
    url = "https://191wsd17nc.execute-api.eu-west-1.amazonaws.com/production/report",
    body  = "{""reportName"":""opportunities"",
        ""reportParams"": {""groupIDs"": all}
    }",
    Parsed_JSON = Json.Document(body),
    BuildQueryString = Uri.BuildQueryString(Parsed_JSON),
    Source = Json.Document(Web.Contents(url,[
        Headers = [#"Content-Type"="application/json", #"x-api-key"="<Your Entity API Key>"],
        Content = Text.ToBinary(body) ] )),
    data = Source[data],
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Select Columns" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"additional_info", "associatedAccounts", "close_date", "closed_status", "created", "currency", "discussions", "endUser1Region", "expected_close_date", "fiscalPeriod", "fiscalQuarter", "fiscalYear", "forecast", "grossMarginDecimal", "groupName", "id", "likely_value", "likelyValueCLF", "likelyValueCLP", "likelyValueCOP", "likelyValueUSD", "marginAmountCLF", "marginAmountCLP", "marginAmountCOP", "marginAmountUSD", "opportunitiesCRMURL", "ownerName", "poc", "productName", "projectLikelihoodGoPercentageDecimal", "proposals", "reps", "status", "title", "winGoProbabilityDecimal", "winLikelihoodGetPercentageDecimal"}, {"additional_info", "associatedAccounts", "close_date", "closed_status", "created", "currency", "discussions", "endUser1Region", "expected_close_date", "fiscalPeriod", "fiscalQuarter", "fiscalYear", "forecast", "grossMarginDecimal", "groupName", "id", "likely_value", "likelyValueCLF", "likelyValueCLP", "likelyValueCOP", "likelyValueUSD", "marginAmountCLF", "marginAmountCLP", "marginAmountCOP", "marginAmountUSD", "opportunitiesCRMURL", "ownerName", "poc", "productName", "projectLikelihoodGoPercentageDecimal", "proposals", "reps", "status", "title", "winGoProbabilityDecimal", "winLikelihoodGetPercentageDecimal"}),
    #"Renamed Columns3" = Table.RenameColumns(#"Select Columns",{{"likely_value", "OppValue"}}),
    #"Renamed Columns" = Table.RenameColumns(#"Renamed Columns3",{{"title", "Title"}, {"productName", "Product"}, {"currency", "Currency"}, {"expected_close_date", "Expected Close Date"}, {"close_date", "Actual Close Date"}, {"additional_info", "Additional Info"}, {"status", "Opportunity Status"}, {"closed_status", "Closed Status"}, {"created", "Created Date"}, {"ownerName", "Owner"}, {"forecast", "Forecast"}, {"groupName", "Group"}, {"endUser1Region", "Region"}, {"grossMarginDecimal", "GM %"}, {"projectLikelihoodGoPercentageDecimal", "Go %"}, {"winLikelihoodGetPercentageDecimal", "Get"}, {"winGoProbabilityDecimal", "Prob %"}, {"fiscalYear", "Estimated Close Date (FY)"}, {"fiscalQuarter", "Estimated Close Date (Q)"}, {"fiscalPeriod", "Estimated Close Date (FY Q)"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"opportunitiesCRMURL"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"likelyValueUSD", "Value $"}, {"marginAmountUSD", "Marg $"}, {"likelyValueCLF", "Value CLF"}, {"marginAmountCLF", "Margin CLF"}, {"likelyValueCLP", "Value CLP"}, {"marginAmountCLP", "Margin CLP"}, {"likelyValueCOP", "Value COP"}, {"marginAmountCOP", "Margin COP"}, {"id", "OppID"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Expected Close Date", type datetime}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Changed Type",{{"Expected Close Date", "Excpected Close Date"}, {"reps", "_RepList"}, {"proposals", "_ProposalList"}, {"associatedAccounts", "_AssociatedAccountsList"}, {"poc", "_POCList"}, {"discussions", "_DiscussionList"}, {"Region", "State"}, {"Get", "Get %"}, {"Estimated Close Date (FY Q)", "'Estimated Close Date (FY YQ)"}, {"Estimated Close Date (Q)", "Estimated Close Date (FY Q)"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Margin CLP", Currency.Type}, {"Value COP", Currency.Type}, {"Margin COP", Currency.Type}, {"Value CLP", Currency.Type}, {"Margin CLF", Currency.Type}, {"Value CLF", Currency.Type},{"Marg $", Currency.Type}, {"Value $", Currency.Type}}),
    #"Renamed Columns4" = Table.RenameColumns(#"Changed Type1",{{"Excpected Close Date", "Expected Close Date"}})
in
    #"Renamed Columns4"

Parameters

Group IDs

body  = "{""reportName"":""opportunities"",
        ""reportParams"": {""groupIDs"": [specific group IDs you'd like to return]}
    }",

Available Fields

 
NameExample ValueData TypeOptions / Notes
additional_infoQAAuto_DescriptionText 
associatedAccounts[List]JSON Array 
attributes[List]JSON Array 
close_date Time / Date 
closed_statusOpenText 
created2021-03-08T12:48:37.000ZTime / Date 
created_by#U#1TextIf the ID has a “U”, it indicates an internal Supplier User followed by their ID. If it has a “C” in indicates a Contact followed by the ID of the contact.
currencyUSDText 
currencyID36Integer 
discussions[List]JSON Array 
endUser1Region   
expected_close_date2019-07-30T00:00:00.000ZTime / Date 
expectedQuarterClose2  
fiscalPeriod2020 Q2  
fiscalQuarter2  
fiscalYear2020  
forecastExcludedDropdownExcluded, Included, Upside
gross_margin10Number 
grossMarginDecimal0.1Number 
groupNameCRMThreeOQAAutoDENGroupNameText 
id1Integer 
likely_currency_id36Integer 
likely_value100Number 
likelyValueAED367.24931Number 
likelyValueAUD134.4986201Number 
likelyValueBRL495.8601656Number 
likelyValueCAD126.4949402Number 
likelyValueCHF93.37626495Number 
likelyValueCLP90793.92824Number 
likelyValueCNY636.8905244Number 
likelyValueEUR91.99632015Number 
likelyValueGBP76.81692732Number 
likelyValueGEL275.0689972Number 
likelyValueHKD783.5326587Number 
likelyValueILS322.3551058Number 
likelyValueINR7605.887764Number 
likelyValueJPY12556.94572Number 
likelyValueMAD381180.3128Number 
likelyValueMXN1988.50046Number 
likelyValueMYR423.1830727Number 
likelyValueQA test Currency919.9632015Number 
likelyValueSEK950.9659614Number 
likelyValueSGD130.6347746Number 
likelyValueTHB3364.397424Number 
likelyValueUSD100Number 
marginAmount10Number 
marginAmountAED36.724931Number 
marginAmountAUD13.44986201Number 
marginAmountBRL49.58601656Number 
marginAmountCAD12.64949402Number 
marginAmountCHF9.337626495Number 
marginAmountCLP9079.392824Number 
marginAmountCNY63.68905244Number 
marginAmountEUR9.199632015Number 
marginAmountGBP7.681692732Number 
marginAmountGEL27.50689972Number 
marginAmountHKD78.35326587Number 
marginAmountILS32.23551058Number 
marginAmountINR760.5887764Number 
marginAmountJPY1255.694572Number 
marginAmountMAD38118.03128Number 
marginAmountMXN198.850046Number 
marginAmountMYR42.31830727Number 
marginAmountQA test Currency91.99632015Number 
marginAmountSEK95.09659614Number 
marginAmountSGD13.06347746Number 
marginAmountTHB336.4397424Number 
marginAmountUSD10Number 
modified2021-03-08T12:48:37.000ZTime / Date 
nameAdmin  
opportunitiesCRMURLhttps://www.ovivo.co/admin/opportunities/view/1  
opportunityProducts[List]JSON Array 
ownerNameQA UserText 
poc[List]JSON Array 
productName   
project_likelihood_go_id0Integer 
project_likelihood_go_percentage   
projectLikelihoodGoPercentageDecimal0  
proposals[List]JSON Array 
rep1_accountNameCRMThreeOQAAutoDENAccountNameText 
rep1_nameCRMThreeOQAAutoDENContactNameText 
reps[List]JSON Array 
statusDeletedTextDeleted, Active
titleCRMThreeOQAAutoDENOpportunityNameText 
updateFromRep2021-03-08T12:48:37.000ZTime / Date 
win_likelihood_get_id0  
win_likelihood_get_percentage   
winGoProbability   
winGoProbabilityDecimal0  
winLikelihoodGetPercentageDecimal0