SQL Server / New FreshBooks / Create Single Invoice Profile w/ Time Entry Holder
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
EXEC @hr = sp_OACreate 'Chilkat_9_5_0.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.
-- {
-- "invoice_profile": {
-- "customerid": 123,
-- "frequency": "m",
-- "profileid": null,
-- "disable": false,
-- "due_offset_days": 30,
-- "numberRecurring": 0,
-- "send_email": true,
-- "include_unbilled_time": true,
-- "project_format": {
-- "group_by": "service",
-- "method": "detailed",
-- "show_tname": true,
-- "show_pname": true,
-- "show_staff": true,
-- "show_date": true,
-- "show_notes": true,
-- "show_total_hours": false
-- },
-- "skip_generation": false,
-- "retainer_id": null,
-- "require_auto_bill": false,
-- "create_date": "2019-07-15",
-- "currency_code": "CAD",
-- "discount_value": 0,
-- "template": "clean-grouped",
-- "terms": "Show me the money",
-- "allowed_gatewayids": [
-- 26
-- ],
-- "late_fee": {
-- "compounded_taxes": false,
-- "days": 30,
-- "enabled": false,
-- "first_tax_name": "HST",
-- "first_tax_percent": 13,
-- "repeat": false,
-- "second_tax_name": null,
-- "second_tax_percent": null,
-- "type": "percent",
-- "value": 10
-- },
-- "late_reminders": [
-- ],
-- "lines": [
-- {
-- "amount": {
-- "amount": "0",
-- "code": "CAD"
-- },
-- "description": null,
-- "expenseid": null,
-- "taxName1": null,
-- "taxAmount1": 0,
-- "name": null,
-- "qty": 1,
-- "taxName2": null,
-- "taxAmount2": 0,
-- "type": 5,
-- "unit_cost": {
-- "amount": "0",
-- "code": "CAD"
-- },
-- "profileid": null
-- }
-- ]
-- }
-- }
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat_9_5_0.JsonObject', @json OUT
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'invoice_profile.customerid', 123
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'invoice_profile.frequency', 'm'
EXEC sp_OAMethod @json, 'UpdateNull', @success OUT, 'invoice_profile.profileid'
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'invoice_profile.disable', 0
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'invoice_profile.due_offset_days', 30
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'invoice_profile.numberRecurring', 0
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'invoice_profile.send_email', 1
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'invoice_profile.include_unbilled_time', 1
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'invoice_profile.project_format.group_by', 'service'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'invoice_profile.project_format.method', 'detailed'
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'invoice_profile.project_format.show_tname', 1
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'invoice_profile.project_format.show_pname', 1
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'invoice_profile.project_format.show_staff', 1
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'invoice_profile.project_format.show_date', 1
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'invoice_profile.project_format.show_notes', 1
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'invoice_profile.project_format.show_total_hours', 0
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'invoice_profile.skip_generation', 0
EXEC sp_OAMethod @json, 'UpdateNull', @success OUT, 'invoice_profile.retainer_id'
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'invoice_profile.require_auto_bill', 0
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'invoice_profile.create_date', '2019-07-15'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'invoice_profile.currency_code', 'CAD'
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'invoice_profile.discount_value', 0
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'invoice_profile.template', 'clean-grouped'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'invoice_profile.terms', 'Show me the money'
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'invoice_profile.allowed_gatewayids[0]', 26
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'invoice_profile.late_fee.compounded_taxes', 0
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'invoice_profile.late_fee.days', 30
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'invoice_profile.late_fee.enabled', 0
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'invoice_profile.late_fee.first_tax_name', 'HST'
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'invoice_profile.late_fee.first_tax_percent', 13
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'invoice_profile.late_fee.repeat', 0
EXEC sp_OAMethod @json, 'UpdateNull', @success OUT, 'invoice_profile.late_fee.second_tax_name'
EXEC sp_OAMethod @json, 'UpdateNull', @success OUT, 'invoice_profile.late_fee.second_tax_percent'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'invoice_profile.late_fee.type', 'percent'
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'invoice_profile.late_fee.value', 10
EXEC sp_OAMethod @json, 'UpdateNewArray', @success OUT, 'invoice_profile.late_reminders'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'invoice_profile.lines[0].amount.amount', '0'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'invoice_profile.lines[0].amount.code', 'CAD'
EXEC sp_OAMethod @json, 'UpdateNull', @success OUT, 'invoice_profile.lines[0].description'
EXEC sp_OAMethod @json, 'UpdateNull', @success OUT, 'invoice_profile.lines[0].expenseid'
EXEC sp_OAMethod @json, 'UpdateNull', @success OUT, 'invoice_profile.lines[0].taxName1'
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'invoice_profile.lines[0].taxAmount1', 0
EXEC sp_OAMethod @json, 'UpdateNull', @success OUT, 'invoice_profile.lines[0].name'
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'invoice_profile.lines[0].qty', 1
EXEC sp_OAMethod @json, 'UpdateNull', @success OUT, 'invoice_profile.lines[0].taxName2'
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'invoice_profile.lines[0].taxAmount2', 0
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'invoice_profile.lines[0].type', 5
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'invoice_profile.lines[0].unit_cost.amount', '0'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'invoice_profile.lines[0].unit_cost.code', 'CAD'
EXEC sp_OAMethod @json, 'UpdateNull', @success OUT, 'invoice_profile.lines[0].profileid'
-- Adds the "Authorization: Bearer <access_token>" header.
EXEC sp_OASetProperty @http, 'AuthToken', '<access_token>'
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Content-Type', 'application/json'
DECLARE @resp int
EXEC sp_OAMethod @http, 'PostJson3', @resp OUT, 'https://api.freshbooks.com/accounting/account/{{accountid}}/invoice_profiles/invoice_profiles?include[]=allowed_gateways,contacts,late_fee,late_reminders,lines,presentation,system,tracking,project_format,total_accrued_revenue&include%5B%5D=allowed_gateways,contacts,late_fee,late_reminders,lines,presentation,system,tracking,project_format,total_accrued_revenue', '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>"
-H "Content-Type: application/json"
-d '{
"invoice_profile":
{
"customerid": {{customerid}},
"frequency":"m",
"profileid":null,
"disable":false,
"due_offset_days":30,
"numberRecurring":0,
"send_email":true,
"include_unbilled_time":true,
"project_format":
{
"group_by":"service",
"method":"detailed",
"show_tname":true,
"show_pname":true,
"show_staff":true,
"show_date":true,
"show_notes":true,
"show_total_hours":false
},
"skip_generation":false,
"retainer_id":null,
"require_auto_bill":false,
"create_date":"2019-07-15",
"currency_code":"CAD",
"discount_value":0,
"template":"clean-grouped",
"terms": "Show me the money",
"allowed_gatewayids":[26],
"late_fee":
{
"compounded_taxes":false,
"days":30,
"enabled":false,
"first_tax_name": "HST",
"first_tax_percent": 13,
"repeat":false,
"second_tax_name":null,
"second_tax_percent":null,
"type":"percent",
"value":10}
,"late_reminders":[],
"lines":
[{
"amount":
{
"amount":"0",
"code":"CAD"
},
"description":null,
"expenseid":null,
"taxName1":null,
"taxAmount1":0,
"name":null,
"qty":1,
"taxName2":null,
"taxAmount2":0,
"type":5,
"unit_cost":
{
"amount":"0",
"code":"CAD"
},
"profileid":null
}]
}
}'
https://api.freshbooks.com/accounting/account/{{accountid}}/invoice_profiles/invoice_profiles?include[]=allowed_gateways,contacts,late_fee,late_reminders,lines,presentation,system,tracking,project_format,total_accrued_revenue&include%5B%5D=allowed_gateways,contacts,late_fee,late_reminders,lines,presentation,system,tracking,project_format,total_accrued_revenue
Postman Collection Item JSON
{
"name": "Create Single Invoice Profile w/ Time Entry Holder ",
"request": {
"method": "POST",
"header": [
{
"key": "Content-Type",
"name": "Content-Type",
"value": "application/json",
"type": "text"
}
],
"body": {
"mode": "raw",
"raw": "{\n \"invoice_profile\":\n {\n \"customerid\": {{customerid}},\n \"frequency\":\"m\",\n \"profileid\":null,\n \"disable\":false,\n \"due_offset_days\":30,\n \"numberRecurring\":0,\n \"send_email\":true,\n \"include_unbilled_time\":true,\n \"project_format\":\n {\n \"group_by\":\"service\",\n \"method\":\"detailed\",\n \"show_tname\":true,\n \"show_pname\":true,\n \"show_staff\":true,\n \"show_date\":true,\n \"show_notes\":true,\n \"show_total_hours\":false\n },\n \"skip_generation\":false,\n \"retainer_id\":null,\n \"require_auto_bill\":false,\n \"create_date\":\"2019-07-15\",\n \"currency_code\":\"CAD\",\n \"discount_value\":0,\n \"template\":\"clean-grouped\",\n \"terms\": \"Show me the money\",\n \"allowed_gatewayids\":[26],\n \"late_fee\":\n {\n \"compounded_taxes\":false,\n \"days\":30,\n \"enabled\":false,\n \"first_tax_name\": \"HST\",\n \"first_tax_percent\": 13,\n \"repeat\":false,\n \"second_tax_name\":null,\n \"second_tax_percent\":null,\n \"type\":\"percent\",\n \"value\":10}\n ,\"late_reminders\":[],\n \"lines\":\n [{\n \"amount\":\n {\n \"amount\":\"0\",\n \"code\":\"CAD\"\n },\n \"description\":null,\n \"expenseid\":null,\n \"taxName1\":null,\n \"taxAmount1\":0,\n \"name\":null,\n \"qty\":1,\n \"taxName2\":null,\n \"taxAmount2\":0,\n \"type\":5,\n \"unit_cost\":\n {\n \"amount\":\"0\",\n \"code\":\"CAD\"\n },\n \"profileid\":null\n }]\n }\n}"
},
"url": {
"raw": "https://api.freshbooks.com/accounting/account/{{accountid}}/invoice_profiles/invoice_profiles?include[]=allowed_gateways,contacts,late_fee,late_reminders,lines,presentation,system,tracking,project_format,total_accrued_revenue&include%5B%5D=allowed_gateways,contacts,late_fee,late_reminders,lines,presentation,system,tracking,project_format,total_accrued_revenue",
"protocol": "https",
"host": [
"api",
"freshbooks",
"com"
],
"path": [
"accounting",
"account",
"{{accountid}}",
"invoice_profiles",
"invoice_profiles"
],
"query": [
{
"key": "include[]",
"value": "allowed_gateways,contacts,late_fee,late_reminders,lines,presentation,system,tracking,project_format,total_accrued_revenue"
},
{
"key": "include%5B%5D",
"value": "allowed_gateways,contacts,late_fee,late_reminders,lines,presentation,system,tracking,project_format,total_accrued_revenue"
}
]
},
"description": "This will create an invoice profile that will pull unbilled time entries for your client on to the invoice that is generated"
},
"response": [
]
}
