SQL Server / Salesforce Platform APIs / Create Order (Evergreen & Termed)
Back to Collection Items
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @iTmp0 int
-- Important: Do not use nvarchar(max). See the warning about using nvarchar(max).
DECLARE @sTmp0 nvarchar(4000)
-- This example assumes the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @http int
-- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
DECLARE @success int
-- Use this online tool to generate code from sample JSON: Generate Code to Create JSON
-- The following JSON is sent in the request body.
-- {
-- "allOrNone": true,
-- "compositeRequest": [
-- {
-- "method": "POST",
-- "url": "/services/data/v{{version}}/sobjects/Order",
-- "referenceId": "refOrder",
-- "body": {
-- "AccountId": "{{defaultAccount}}",
-- "EffectiveDate": "2022-01-01",
-- "Pricebook2Id": "{{standardPricebook}}",
-- "BillToContactId": "{{defaultBillToContact}}",
-- "Status": "Draft"
-- }
-- },
-- {
-- "method": "POST",
-- "url": "/services/data/v{{version}}/sobjects/AppUsageAssignment",
-- "referenceId": "refAppTag",
-- "body": {
-- "AppUsageType": "SubscriptionManagement",
-- "RecordId": "@{refOrder.id}"
-- }
-- },
-- {
-- "method": "POST",
-- "url": "/services/data/v{{version}}/sobjects/OrderAction",
-- "referenceId": "refOrderAction",
-- "body": {
-- "OrderId": "@{refOrder.id}",
-- "Type": "New Sale"
-- }
-- },
-- {
-- "method": "POST",
-- "url": "/services/data/v{{version}}/sobjects/OrderItem",
-- "referenceId": "Item_1_Evergreen",
-- "body": {
-- "OrderId": "@{refOrder.id}",
-- "OrderActionId": "@{refOrderAction.id}",
-- "PricebookEntryId": "{{VirtualRouterEvergrnPBE}}",
-- "Quantity": 2.0,
-- "UnitPrice": 25.0,
-- "NetUnitPrice": 25.0,
-- "TotalLineAmount": 50.0,
-- "PeriodBoundary": "Anniversary",
-- "BillingFrequency2": "Monthly",
-- "ServiceDate": "2022-02-01",
-- "TaxTreatmentId": "{{defaultTaxTreatment}}"
-- }
-- },
-- {
-- "method": "POST",
-- "url": "/services/data/v{{version}}/sobjects/OrderItem",
-- "referenceId": "Item_2_termed",
-- "body": {
-- "OrderId": "@{refOrder.id}",
-- "OrderActionId": "@{refOrderAction.id}",
-- "PricebookEntryId": "{{VirtualRouterTermedPBE}}",
-- "Quantity": 10.0,
-- "UnitPrice": 50.0,
-- "NetUnitPrice": 50.0,
-- "TotalLineAmount": 500.0,
-- "PeriodBoundary": "Anniversary",
-- "BillingFrequency2": "Monthly",
-- "PricingTermCount": 12.0,
-- "ServiceDate": "2022-02-01",
-- "EndDate": "2023-01-01",
-- "TaxTreatmentId": "{{defaultTaxTreatment}}"
-- }
-- }
-- ]
-- }
DECLARE @json int
-- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'allOrNone', 1
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[0].method', 'POST'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[0].url', '/services/data/v{{version}}/sobjects/Order'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[0].referenceId', 'refOrder'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[0].body.AccountId', '{{defaultAccount}}'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[0].body.EffectiveDate', '2022-01-01'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[0].body.Pricebook2Id', '{{standardPricebook}}'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[0].body.BillToContactId', '{{defaultBillToContact}}'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[0].body.Status', 'Draft'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[1].method', 'POST'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[1].url', '/services/data/v{{version}}/sobjects/AppUsageAssignment'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[1].referenceId', 'refAppTag'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[1].body.AppUsageType', 'SubscriptionManagement'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[1].body.RecordId', '@{refOrder.id}'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[2].method', 'POST'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[2].url', '/services/data/v{{version}}/sobjects/OrderAction'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[2].referenceId', 'refOrderAction'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[2].body.OrderId', '@{refOrder.id}'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[2].body.Type', 'New Sale'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[3].method', 'POST'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[3].url', '/services/data/v{{version}}/sobjects/OrderItem'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[3].referenceId', 'Item_1_Evergreen'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[3].body.OrderId', '@{refOrder.id}'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[3].body.OrderActionId', '@{refOrderAction.id}'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[3].body.PricebookEntryId', '{{VirtualRouterEvergrnPBE}}'
EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'compositeRequest[3].body.Quantity', '2.0'
EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'compositeRequest[3].body.UnitPrice', '25.0'
EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'compositeRequest[3].body.NetUnitPrice', '25.0'
EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'compositeRequest[3].body.TotalLineAmount', '50.0'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[3].body.PeriodBoundary', 'Anniversary'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[3].body.BillingFrequency2', 'Monthly'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[3].body.ServiceDate', '2022-02-01'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[3].body.TaxTreatmentId', '{{defaultTaxTreatment}}'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[4].method', 'POST'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[4].url', '/services/data/v{{version}}/sobjects/OrderItem'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[4].referenceId', 'Item_2_termed'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[4].body.OrderId', '@{refOrder.id}'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[4].body.OrderActionId', '@{refOrderAction.id}'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[4].body.PricebookEntryId', '{{VirtualRouterTermedPBE}}'
EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'compositeRequest[4].body.Quantity', '10.0'
EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'compositeRequest[4].body.UnitPrice', '50.0'
EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'compositeRequest[4].body.NetUnitPrice', '50.0'
EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'compositeRequest[4].body.TotalLineAmount', '500.0'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[4].body.PeriodBoundary', 'Anniversary'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[4].body.BillingFrequency2', 'Monthly'
EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'compositeRequest[4].body.PricingTermCount', '12.0'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[4].body.ServiceDate', '2022-02-01'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[4].body.EndDate', '2023-01-01'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'compositeRequest[4].body.TaxTreatmentId', '{{defaultTaxTreatment}}'
-- Adds the "Authorization: Bearer <access_token>" header.
EXEC sp_OASetProperty @http, 'AuthToken', '<access_token>'
DECLARE @resp int
EXEC sp_OAMethod @http, 'PostJson3', @resp OUT, 'https://domain.com/services/data/v{{version}}/composite', 'application/json', @json
EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @json
RETURN
END
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT @iTmp0
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @json
END
GO
Curl Command
curl -X POST
-H "Authorization: Bearer <access_token>"
-d '{
"allOrNone": true,
"compositeRequest": [
{
"method": "POST",
"url": "/services/data/v{{version}}/sobjects/Order",
"referenceId": "refOrder",
"body": {
"AccountId": "{{defaultAccount}}",
"EffectiveDate": "2022-01-01",
"Pricebook2Id": "{{standardPricebook}}",
"BillToContactId": "{{defaultBillToContact}}",
"Status": "Draft"
}
},
{
"method": "POST",
"url": "/services/data/v{{version}}/sobjects/AppUsageAssignment",
"referenceId": "refAppTag",
"body": {
"AppUsageType": "SubscriptionManagement",
"RecordId": "@{refOrder.id}"
}
},
{
"method": "POST",
"url": "/services/data/v{{version}}/sobjects/OrderAction",
"referenceId": "refOrderAction",
"body": {
"OrderId": "@{refOrder.id}",
"Type": "New Sale"
}
},
{
"method": "POST",
"url": "/services/data/v{{version}}/sobjects/OrderItem",
"referenceId": "Item_1_Evergreen",
"body": {
"OrderId": "@{refOrder.id}",
"OrderActionId": "@{refOrderAction.id}",
"PricebookEntryId": "{{VirtualRouterEvergrnPBE}}",
"Quantity": 2.0,
"UnitPrice": 25.0,
"NetUnitPrice": 25.0,
"TotalLineAmount": 50.0,
"PeriodBoundary": "Anniversary",
"BillingFrequency2": "Monthly",
"ServiceDate": "2022-02-01",
"TaxTreatmentId": "{{defaultTaxTreatment}}"
}
},
{
"method": "POST",
"url": "/services/data/v{{version}}/sobjects/OrderItem",
"referenceId": "Item_2_termed",
"body": {
"OrderId": "@{refOrder.id}",
"OrderActionId": "@{refOrderAction.id}",
"PricebookEntryId": "{{VirtualRouterTermedPBE}}",
"Quantity": 10.0,
"UnitPrice": 50.0,
"NetUnitPrice": 50.0,
"TotalLineAmount": 500.0,
"PeriodBoundary": "Anniversary",
"BillingFrequency2": "Monthly",
"PricingTermCount": 12.0,
"ServiceDate": "2022-02-01",
"EndDate": "2023-01-01",
"TaxTreatmentId": "{{defaultTaxTreatment}}"
}
}
]
}'
https://domain.com/services/data/v{{version}}/composite
Postman Collection Item JSON
{
"name": "Create Order (Evergreen & Termed)",
"request": {
"method": "POST",
"header": [
],
"body": {
"mode": "raw",
"raw": "{\n \"allOrNone\": true,\n \"compositeRequest\": [\n {\n \"method\": \"POST\",\n \"url\": \"/services/data/v{{version}}/sobjects/Order\",\n \"referenceId\": \"refOrder\",\n \"body\": {\n \"AccountId\": \"{{defaultAccount}}\",\n \"EffectiveDate\": \"2022-01-01\",\n \"Pricebook2Id\": \"{{standardPricebook}}\",\n \"BillToContactId\": \"{{defaultBillToContact}}\",\n \"Status\": \"Draft\"\n }\n },\n {\n \"method\": \"POST\",\n \"url\": \"/services/data/v{{version}}/sobjects/AppUsageAssignment\",\n \"referenceId\": \"refAppTag\",\n \"body\": {\n \"AppUsageType\": \"SubscriptionManagement\",\n \"RecordId\": \"@{refOrder.id}\"\n }\n },\n {\n \"method\": \"POST\",\n \"url\": \"/services/data/v{{version}}/sobjects/OrderAction\",\n \"referenceId\": \"refOrderAction\",\n \"body\": {\n \"OrderId\": \"@{refOrder.id}\",\n \"Type\": \"New Sale\"\n }\n },\n {\n \"method\": \"POST\",\n \"url\": \"/services/data/v{{version}}/sobjects/OrderItem\",\n \"referenceId\": \"Item_1_Evergreen\",\n \"body\": {\n \"OrderId\": \"@{refOrder.id}\",\n \"OrderActionId\": \"@{refOrderAction.id}\",\n \"PricebookEntryId\": \"{{VirtualRouterEvergrnPBE}}\",\n \"Quantity\": 2.0,\n \"UnitPrice\": 25.0,\n \"NetUnitPrice\": 25.0,\n \"TotalLineAmount\": 50.0,\n \"PeriodBoundary\": \"Anniversary\",\n \"BillingFrequency2\": \"Monthly\", \n \"ServiceDate\": \"2022-02-01\",\n \"TaxTreatmentId\": \"{{defaultTaxTreatment}}\"\n }\n },\n {\n \"method\": \"POST\",\n \"url\": \"/services/data/v{{version}}/sobjects/OrderItem\",\n \"referenceId\": \"Item_2_termed\",\n \"body\": {\n \"OrderId\": \"@{refOrder.id}\",\n \"OrderActionId\": \"@{refOrderAction.id}\",\n \"PricebookEntryId\": \"{{VirtualRouterTermedPBE}}\",\n \"Quantity\": 10.0,\n \"UnitPrice\": 50.0,\n \"NetUnitPrice\": 50.0,\n \"TotalLineAmount\": 500.0,\n \"PeriodBoundary\": \"Anniversary\",\n \"BillingFrequency2\": \"Monthly\",\n \"PricingTermCount\": 12.0, \n \"ServiceDate\": \"2022-02-01\",\n \"EndDate\": \"2023-01-01\",\n \"TaxTreatmentId\": \"{{defaultTaxTreatment}}\"\n }\n } \n ]\n}",
"options": {
"raw": {
"language": "json"
}
}
},
"url": {
"raw": "{{_endpoint}}/services/data/v{{version}}/composite",
"host": [
"{{_endpoint}}"
],
"path": [
"services",
"data",
"v{{version}}",
"composite"
]
},
"description": "Creates a Subscription Management order with two order items. One order item is sold on a monthly termed subscription, and the other order item is sold as an evergreen subscription."
},
"response": [
]
}
