My requirement is to retrieve invoice journal information from the ZIP application into D365 Finance & Operations.
In ZIP, the information is stored in JSON format. In the code, I’m accessing the ZIP API using the API key provided by the client. I decode the JSON response from the API and store the information into custom tables in D365.
From these custom tables, I will then create the invoice journals.
.......
using System.Net;
using System.Net.Security;
using System.IO.Path;
using Newtonsoft.Json.Linq.JObject;
using System.Collections;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
public class PROZIPVendorInvoiceIntegrationService extends SysOperationServiceBase
{
PROZIPVendorInvoiceStagingTable VendorInvoiceStagingTable,VendorInvoiceStagingTableUpd;
PROZIPVendorInvoiceLineStagingTable VendorInvoiceLineStagingTable;
public str getZIPRecords(str Url, str method)
{
System.Net.HttpWebRequest request;
System.Net.WebResponse response;
System.IO.Stream responseStream, requestStream;
System.IO.StreamReader streamReader;
System.Text.Encoding utf8;
System.Byte[] bytes;
System.Exception ex;
Notes token, requestJson, responseJson, errorMessage;
URL endpointURL;
SysInfoLogEnumerator infoLogEnum;
SysInfologMessageStruct infoMessageStruct;
System.Net.WebHeaderCollection headers;
VendParameters vendparameters = VendParameters::find();
//str getStatusResponse;
System.Net.WebException webException;
try
{
System.Net.WebHeaderCollection httpHeader = new System.Net.WebHeaderCollection();
//endpointURL = _Url;//"https://api.ziphq.com/vendors?include_attributes=true";//this.get_EndpointURL(); // Getting API URL
endpointURL = "https://api.ziphq.com/invoices?include_attributes=true";//this.get_EndpointURL(); // Getting API URL
new InteropPermission(InteropKind::ClrInterop).assert();
request = System.Net.WebRequest::Create(endpointURL);
utf8 = System.Text.Encoding::get_UTF8();
headers = new System.Net.WebHeaderCollection();
headers.Add(strFmt('Zip-Api-Key:%1',vendparameters.PROZIPKey));
// headers.Add('accept:application/json');
request.set_Headers(headers);
request.set_Method(_method);
request.set_ContentType('application/json');
request.set_Accept('application/json');
response = request.GetResponse();
responseStream = response.GetResponseStream();
streamReader = new System.IO.StreamReader(responseStream);
responseJson = streamReader.ReadToEnd();
info(strFmt('Success API Response is %1', responseJson));
//this.readStatusResponse(responseJson);
//getStatusResponse = new Amir_SuccessStatusResponse();
//getStatusResponse = FormJsonSerializer::deserializeObject(classNum(Amir_SuccessStatusResponse), responseJson);
}
catch(webException)
{
// Amir_ErrorStatusResponse errorStatusResponse = new Amir_ErrorStatusResponse();
// Handle WebException for error response
if (webException.get_Response() != null)
{
System.Net.HttpWebResponse httpWebResponse;
Notes responseString;
// Get error response stream
httpWebResponse = webException.get_Response() as System.Net.HttpWebResponse;
responseStream = httpWebResponse.GetResponseStream();
streamReader = new System.IO.StreamReader(responseStream);
responseString = streamReader.ReadToEnd();
// Log the error response body (usually in JSON format)
error(strFmt("Error Response (JSON): %1", responseString));
//errorStatusResponse = FormJsonSerializer::deserializeObject(classNum(Amir_ErrorStatusResponse), responseString);
}
}
return responseJson;
}
public void processOperation()
{
VendParameters vendParmeters = VendParameters::find();
str response = this.getZIPRecords(strFmt('%1?include_attributes=true', vendParmeters.PROZIPVendorInvoiceURL), 'GET');
if (response != null)
{
this.readStatusResponse(response);
}
}
/*Public static void Main(Args _args)
{
PROZIPVendorIntegrationService PROZIPVendorIntegrationService = new PROZIPVendorIntegrationService();
VendParameters vendParmeters = VendParameters::find();
str response = PROZIPVendorIntegrationService.getZIPRecords(strFmt('%1?include_attributes=true', vendParmeters.PROZIPVendorURL), 'GET');
if (response != null)
{
PROZIPVendorIntegrationService.readStatusResponse(response);
}
}*/
boolean readStatusResponse(str _response)
{
boolean ret;
Newtonsoft.Json.Linq.JObject jObject;
jObject = Newtonsoft.Json.Linq.JObject::Parse(_response);
RecordInsertList insertList = new RecordInsertList(tableNum(PROZIPVendorInvoiceStagingTable));
//jToken = jObject.get_Item('data');
//jObject = Newtonsoft.Json.Linq.JObject::Parse(jToken.ToString());
Newtonsoft.Json.Linq.JArray jarray = jObject.get_Item('list');
int countnum = jarray.Count;
int rowCount;
IEnumerator enumerator = jArray.GetEnumerator();
//ttsbegin;
while (enumerator.MoveNext())
{
rowCount++;
try
{
//if (rowCount == 43)
//{
//Header payload
ttsbegin;
JToken token = enumerator.Current;
str ID = token.get_Item('id').ToString();
str type = token.get_Item('type').ToString();
str description = token.get_item('description').tostring();
str invoice_number = token.get_Item('invoice_number').ToString();
str tax_amount = token.get_Item('tax_amount').ToString();
str currency = token.get_Item('currency').ToString();
str created_at = token.get_Item('created_at').ToString();
str amount = token.get_Item('amount').ToString();
str invoice_date = token.get_Item('invoice_date').ToString();
str source = token.get_Item('source').ToString();
str due_date = token.get_Item('due_date').ToString();
str shipping_amount = token.get_Item('shipping_amount').ToString();
str misc_amount = token.get_Item('misc_amount').ToString();
str status = token.get_Item('status').ToString();;
Newtonsoft.Json.Linq.JObject departmentsObject = token.get_Item('department') as Newtonsoft.Json.Linq.JObject;
str departments, name ,type2;
if (departmentsObject)
{
type2 = departmentsObject.get_item('type').ToString();
if(type2 == 'department')
{
departments = departmentsObject.get_item('name').ToString();
}
}
Newtonsoft.Json.Linq.JObject vendorObject = token.get_Item('vendor') as Newtonsoft.Json.Linq.JObject;
str vendName ;
str vendAccount;
if(vendorObject)
{
vendName = vendorObject.get_item('name').ToString();
Newtonsoft.Json.Linq.JArray vendorAccountloc = vendorObject.get_Item('external_data') as Newtonsoft.Json.Linq.JArray;
System.Collections.IEnumerator attrEnumerator = vendorAccountloc.GetEnumerator();
while (attrEnumerator.MoveNext())
{
Newtonsoft.Json.Linq.JObject vendorObjectId = attrEnumerator.get_Current() as Newtonsoft.Json.Linq.JObject;
vendAccount = vendorObjectId.get_item('id').ToString();
}
}
select firstonly VendorInvoiceStagingTable
where VendorInvoiceStagingTable.ID == ID;
if (!VendorInvoiceStagingTable)
{
VendorInvoiceStagingTable.clear();
VendorInvoiceStagingTable.ID = ID;
VendorInvoiceStagingTable.ProcessingStatus = PROProcessingStatus::Unprocessed;
VendorInvoiceStagingTable.VendAccount = vendAccount;
VendorInvoiceStagingTable.VendName = vendName;
VendorInvoiceStagingTable.Status = status;
VendorInvoiceStagingTable.insert();
}
else if(VendorInvoiceStagingTable.ProcessingStatus != PROProcessingStatus::Processed)
{
ttsbegin;
VendorInvoiceStagingTable.selectForUpdate(true);
VendorInvoiceStagingTable.ID = ID;
VendorInvoiceStagingTable.type = type;
if(pmfTableHasChanged(VendorInvoiceStagingTable))
{
VendorInvoiceStagingTable.ProcessingStatus = PROProcessingStatus::Unprocessed;
}
VendorInvoiceStagingTable.ProcessingRemarks = "";
VendorInvoiceStagingTable.VendAccount = vendAccount;
VendorInvoiceStagingTable.VendName = vendName;
VendorInvoiceStagingTable.update();
ttscommit;
}
//Lines payload
Newtonsoft.Json.Linq.JArray attributeArray = token.get_Item('line_items') as Newtonsoft.Json.Linq.JArray;
System.Collections.IEnumerator attrEnumerator = attributeArray.GetEnumerator();
while (attrEnumerator.MoveNext())
{
Newtonsoft.Json.Linq.JObject attrObject = attrEnumerator.get_Current() as Newtonsoft.Json.Linq.JObject;
str idloc = attrObject.get_Item('id').ToString();
str typeloc = attrObject.get_Item('type').ToString();
str quantity = attrObject.get_Item('quantity').ToString();
str end_date = attrObject.get_Item('end_date').ToString();
str secondary_line_type = attrObject.get_Item('secondary_line_type').ToString();
str amortization_end_date = attrObject.get_Item('amortization_end_date').ToString();
str currencyloc = attrObject.get_Item('currency').ToString();
str amortization_start_date = attrObject.get_Item('amortization_start_date').ToString();
str total = attrObject.get_Item('total').ToString();
str end_date_iso = attrObject.get_Item('end_date_iso').ToString();
str rate = attrObject.get_Item('rate').ToString();
str line_type = attrObject.get_Item('line_type').ToString();
str descriptionloc = attrObject.get_Item('description').ToString();
str start_date_iso = attrObject.get_Item('start_date_iso').ToString();
str start_date = attrObject.get_Item('start_date').ToString();
//Newtonsoft.Json.Linq.JObject departmentsObjectloc = attrObject.get_Item('department') as Newtonsoft.Json.Linq.JObject;
//str departmentsloc, nameloc ,type2loc;
//if (departmentsObjectloc)
//{
// type2loc = departmentsObjectloc.get_item('type').ToString();
// if(type2loc == 'department')
// departmentsloc = departmentsObjectloc.get_item('name').ToString();
//}
str expense_category = attrObject.get_Item('expense_category').ToString();
str location = attrObject.get_Item('location').ToString();
str gl_code = attrObject.get_Item('gl_code').ToString();
Newtonsoft.Json.Linq.JArray procArray = attrObject.get_Item('attributes') as Newtonsoft.Json.Linq.JArray;
System.Collections.IEnumerator procEnumerator = procArray.GetEnumerator();
str procConfigid,procData,taxConfigid,taxData;
str nameloc,countryname;
while(procEnumerator.moveNext())
{
Newtonsoft.Json.Linq.JObject procObject = procEnumerator.get_Current() as Newtonsoft.Json.Linq.JObject;
str procName = procObject.get_Item('name').ToString();
if(procname == "Procurement Categories")
{
procConfigid = procObject.get_Item('config_id').ToString();
procData = procObject.get_Item('data').ToString();
}
if(procName == "Standard tax code")
{
taxConfigid = procObject.get_Item('config_id').ToString();
taxData = procObject.get_Item('data').ToString();
}
if (procName == 'department')
{
Newtonsoft.Json.Linq.JObject departData = procObject.get_Item('data');
nameloc = departData.get_item('name').ToString();
}
if (procName == 'Country')
{
// Newtonsoft.Json.Linq.JObject countryData = procObject.get_Item('data');
countryname = procObject.get_item('data').ToString();
}
}
select firstonly VendorInvoiceLineStagingTable
where VendorInvoiceLineStagingTable.LineItemID == idloc;
if(!VendorInvoiceLineStagingTable)
{
VendorInvoiceLineStagingTable.ID = ID;
VendorInvoiceLineStagingTable.LineItemID = idloc;
VendorInvoiceLineStagingTable.insert();
}
else
{
ttsbegin;
VendorInvoiceLineStagingTable.selectForUpdate(true);
VendorInvoiceLineStagingTable.ID = ID;
VendorInvoiceLineStagingTable.LineItemID = idloc;
VendorInvoiceLineStagingTable.update();
ttscommit;
}
}
Info(strFmt('%1,%2,%3, %4', ID, currency, type, Amount));
ttscommit;
// }
}
catch
{
continue;
}
}
insertList.insertDatabase();
//info(strFmt("Row count %1", rowCount));
//ttscommit;
return ret;
}
}
------------------------------
Payload ;
"list": [ {
"id": "067f50d7-d50a-7695-8000-ebbcb3813dff",
"type": "invoice",
"due_date": 1744070400,
"description": "TEST",
"shipping_amount": "0.00",
"invoice_number": "TEST12345",
"currency": "GBP",
"created_at": 1744113021,
"source": null,
"amount": "60000.00",
"tax_amount": "10000.00",
"invoice_date": "2025-04-08",
"misc_amount": "0.00",
"vendor": {
"id": "067bdfc4-0175-7d63-8000-3b42f209646c",
"type": "vendor",
"name": "Demo Vendor",
"external_data": []
},
"subsidiary": {
"id": "0673684d-ff3b-768d-8000-27ba51fb8a12",
"type": "subsidiary",
"name": "Trainline.com Ltd"
},
"department": {
"id": "0673684b-c28e-78c3-8000-d27a49198bd2",
"type": "department",
"name": "607 Product"
},
"location": null,
"posting_period": null,
"line_items": [
{
"id": "067f50f5-2934-7c27-8000-fcf78aa2def5",
"type": "line_item",
"description": "TEST",
"amortization_start_date": null,
"currency": "USD",
"start_date_iso": null,
"amortization_end_date": null,
"total": "60000",
"quantity": "1",
"end_date_iso": null,
"secondary_line_type": null,
"rate": "50000",
"end_date": null,
"start_date": null,
"line_type": 0,
"item_account": null,
"department": {
"id": "0673684b-c28e-78c3-8000-d27a49198bd2",
"type": "department",
"name": "607 Product"
},
"matched_purchase_order_line_item": null,
"expense_category": null,
"location": null,
"gl_code": null,
"amortization_schedule": {
"start_date": null,
"end_date": null,
"name": null
},
"attributes": [
{
"id": "067f50f5-2b20-767a-8000-f5f0371250ac",
"config_id": "067d9ce9-91a5-77ad-8000-323089dcc532",
"name": "Unit price",
"data": "50000",
"integration_data": null
},
{
"id": "067f50f5-2b21-7fc4-8000-177614b60e17",
"config_id": "067d9ce9-860d-7844-8000-a4ee87420651",
"name": "Department",
"data": {
"id": "0673684b-c28e-78c3-8000-d27a49198bd2",
"type": "department",
"display_name": "607 Product",
"name": "607 Product"
},
"integration_data": null
},
{
"id": "067f50f5-2b23-74c7-8000-35b5ab94c21b",
"config_id": "067d9ce9-9018-7848-8000-55627a0fe891",
"name": "Line description",
"data": "TEST",
"integration_data": null
},
{
"id": "067f50f5-2b24-7861-8000-da384410126b",
"config_id": "067d9ce9-7f40-7978-8000-246020c9fcdb",
"name": "Total price",
"data": "60000",
"integration_data": null
},
{
"id": "067f50f5-2b25-7be5-8000-fb9239115cb8",
"config_id": "067d9ce9-91a9-7ff0-8000-662f279f4ae5",
"name": "Total tax amount",
"data": "10000",
"integration_data": null
},
{
"id": "067f50f5-2b26-7e41-8000-d5a7879a9a77",
"config_id": "067d9ce9-a7ef-76a5-8000-0ec3dd2b2599",
"name": "Standard tax amount",
"data": "10000",
"integration_data": null
},
{
"id": "067f50f5-2b28-70e1-8000-c9404ff8e8c5",
"config_id": "067d9ce9-a7f3-7e28-8000-4b212118e441",
"name": "Withholding tax amount",
"data": null,
"integration_data": null
},
{
"id": "067f50f5-2b29-731a-8000-a03da8d04799",
"config_id": "067d9ce9-901f-7d84-8000-a24ceee266ad",
"name": "Line type",
"data": 0,
"integration_data": null
},
{
"id": "067f50f5-2b2a-7834-8000-e81059efc876",
"config_id": "067d9ce9-9029-7ba8-8000-10474fa073ab",
"name": "Quantity",
"data": "1",
"integration_data": null
},
{
"id": "067f50f5-2b2b-7b10-8000-3a1cd5003646",
"config_id": "067d9ce9-8623-72cf-8000-4991cb4be98c",
"name": "Category",
"data": null,
"integration_data": null
},
{
"id": "067f50f5-2c89-7563-8000-7cde80bfdd63",
"config_id": "067d9ce9-8627-760e-8000-78257d9dee11",
"name": "Account",
"data": null,
"integration_data": null
},
{
"id": "067f50f5-3073-75aa-8000-cfac10f0aaad",
"config_id": "067d9ce9-85fc-7fdd-8000-d68e25960133",
"name": "Currency",
"data": "USD",
"integration_data": null
},
{
"id": "067f50f5-3075-7602-8000-78b348f53e4a",
"config_id": "067ea7a0-98c6-7343-8000-f0040adaa93b",
"name": "Procurement Categories",
"data": "0003 - Maint/support software system costs",
"integration_data": {
"id": "067d14e7-a1fc-785c-8000-fd18784793c1",
"type": "lookup_option",
"key": "0003",
"name": "0003 - Maint/support software system costs"
}
},
{
"id": "067f50f5-971d-7a5f-8000-7991435a23aa",
"config_id": "067dafa9-006e-7c87-8000-d50222032c1f",
"name": "Country",
"data": "UK",
"integration_data": {
"id": "067b369f-7b24-7118-8000-41bcacfd7d84",
"type": "lookup_option",
"key": null,
"name": "UK"
}
},
{
"id": "067f50f8-35a0-745a-8000-944821ad7ce3",
"config_id": "067e52ad-c1e4-7096-8000-e71d9a609bb1",
"name": "Standard Tax Code (Temp)",
"data": "UK-STD",
"integration_data": {
"id": "067eee01-44ee-7f2b-8000-00fdaaba807e",
"type": "lookup_option",
"key": null,
"name": "UK-STD"
}
},
{
"id": "067f50f8-d619-7b82-8000-24a4b9295680",
"config_id": "067d9ce9-a7f8-7473-8000-0b0ec0423e12",
"name": "Standard tax code",
"data": "UK-STD*UK Standard 20% (qjl) - 20%",
"integration_data": null
},
{
"id": "067f50f8-d61b-7377-8000-8b35865c0c5d",
"config_id": "067d9ce9-a7fb-73e2-8000-723a9e5fe4c6",
"name": "Withholding tax code",
"data": null,
"integration_data": null
}
]
}
],
"payment_terms": null,
"external_id": null,
"approved_at": 1744113590,
"attributes": [
{
"id": "067f50d7-d65f-7d79-8000-359c75a2191a",
"config_id": "067d9ce9-b34b-7334-8000-be9d9da13fd6",
"name": "Invoice Upload Source",
"data": 2,
"integration_data": null
},
{
"id": "067f50d7-d8da-7c2a-8000-629cef08782a",
"config_id": "067d9ce9-85fb-7723-8000-0ae5f957d3d7",
"name": "Currency",
"data": "GBP",
"integration_data": null
},
{
"id": "067f50d8-3ed2-7e7c-8000-ea6afd7a1307",
"config_id": "067d9ce9-900e-790e-8000-1e2554f3facd",
"name": "Vendor",
"data": "067bdfc4-0175-7d63-8000-3b42f209646c",
"integration_data": null
},
{
"id": "067f50d8-4393-7711-8000-e55c76069af4",
"config_id": "067d9ce9-8609-7dfc-8000-0fade80e750f",
"name": "Department",
"data": {
"id": "0673684b-c28e-78c3-8000-d27a49198bd2",
"type": "department",
"display_name": "607 Product",
"name": "607 Product"
},
"integration_data": null
},
{
"id": "067f50da-569e-7577-8000-92cfe6bbcfe9",
"config_id": "067d9ce9-b34e-776e-8000-cff1d1e3a2c3",
"name": "Invoice number",
"data": "TEST12345",
"integration_data": null
},
{
"id": "067f50da-7f22-7f3e-8000-71a8538ce561",
"config_id": "067d9ce9-b351-7930-8000-d5766b1047e9",
"name": "Invoice date",
"data": "2025-04-08 00:00:00",
"integration_data": null
},
{
"id": "067f50da-7f24-77ee-8000-d475a3971fc4",
"config_id": "067d9ce9-b357-7968-8000-f264790e6461",
"name": "Posting date",
"data": "2025-04-08 00:00:00",
"integration_data": null
},
{
"id": "067f50da-e14d-738c-8000-f18d2fe7b1b9",
"config_id": "067d9ce9-b354-799a-8000-17f886e2358b",
"name": "Due date",
"data": "2025-04-08 00:00:00",
"integration_data": null
},
{
"id": "067f50db-7540-7312-8000-f2cc070f2b0b",
"config_id": "067d9ce9-b40a-7242-8000-81d10e25eb86",
"name": "Pre-tax total",
"data": "50000",
"integration_data": null
},
{
"id": "067f50db-7541-7c04-8000-8eb44ab5410f",
"config_id": "067d9ce9-7f3a-7c98-8000-df3b05ec5689",
"name": "Total amount",
"data": "60000",
"integration_data": null
},
{
"id": "067f50db-f555-7c32-8000-99a50bb1a3ee",
"config_id": "067d9ce9-b4ad-796c-8000-0aea11da8f0e",
"name": "Standard tax amount",
"data": "10000",
"integration_data": null
},
{
"id": "067f50db-f557-749c-8000-7577ea801d62",
"config_id": "067d9ce9-b410-76c6-8000-58f36c59ea4e",
"name": "Tax amount",
"data": "10000",
"integration_data": null
},
{
"id": "067f50dc-2eb7-78a6-8000-724d67b8de72",
"config_id": "067d9ce9-9015-7a6e-8000-faada2dd2f4e",
"name": "Description",
"data": "TEST",
"integration_data": null
},
{
"id": "067f50dc-5216-7c18-8000-fa95065b31d1",
"config_id": "067d9ce9-861a-7781-8000-3794c6a91d1d",
"name": "Subsidiary",
"data": {
"id": "0673684d-ff3b-768d-8000-27ba51fb8a12",
"type": "subsidiary",
"display_name": "Trainline.com Ltd",
"name": "Trainline.com Ltd"
},
"integration_data": null
},
{
"id": "067f50fa-8e1b-716a-8000-c8d6aca8ddd5",
"config_id": "067d9ce9-9008-7a47-8000-0f463db5de94",
"name": "Requester",
"data": {
"id": "0673f688-e4d0-78e1-8000-5b8bef8b4a5c",
"type": "user",
"email": "david.allamby+sbx@thetrainline.com",
"employee_number": null,
"last_name": "Allamby",
"first_name": "David"
},
"integration_data": null
}
],
"purchase_orders": [],
"payment_method": {
"id": "067eef5b-e59e-7e7a-8000-fc19f911643f",
"external_data": []
},
"payouts": [],
"updated_at": 1744113591,
"applied_vendor_credits": [],
"status": "APPROVED"
}].