NCE Sync for ConnectWise
Updated
by Support
CSP Portal's sync solution for ConnectWise creates a facsimile of your Dicker Data invoice using Additions on Agreements.
A new set of additions will be generated per month, with the intended output as one addition per invoice line item.
- Invoice Quantity
- Total Price
- Billable Amount (Calculated by CSP Portal)
Effective and Cancelled dates are set as the Invoice Date from Dicker Data:
Using the invoice details above, this article will walk through the fields and formulas used to sync an example line item.
Calculating Cost Price
Summary
- CSP Portal determines if a line item is for a full or partial term using Usage Start Date and Usage End Date values
- For a partial term, CSP Portal calculates a prorated percentage value based on the of days in the term divided by the expected days in a full term
- To calculate the unit sell price for your customer's invoice, this prorated percentage value is applied to the sell price of the mapped item
- The cost price for your customer's invoice is taken directly from DickerData's invoice
Field References
CSP Portal uses the following values available on your Dicker Data invoice to determine the Cost Price of a line item:
- Stock Code
- Usage Start Date
- Usage End Date
- Term Start Date
- Term End Date
These values are built from the following Dicker Data API response:
{
"Stockcode": "P1M:CFQ7TTC0LH04:0001",
"UsageStart": "19-JAN-2024",
"UsageEnd": "18-FEB-2024",
"LineAmount": "32.30",
"TermStart": "19-JAN-2024",
"TermEnd": "18-FEB-2024"
},Calculating Total Days
Total Days is the denominator in the Proration Percentage calculation:
Prorated Percent = Days in Term / Total Days
The Usage Start Date and Usage End Date values determine whether a license has been billed for a full month, or a partial month
In most scenarios, Total Days is set to the count of days in the Usage Start Date's month.
Exceptions to this rule are listed below:
Feb Exception
If the usage ends in February (on the 28th or later) and starts in January (on the 29th or later), we use the total days in February for the total days.
End of Month Exception
If the usage ends on the 29th of a month other than February and starts on the 31st, we set the total days to 30.
Start and End Month
If the usage starts and ends in the same month but does not span the entire month, we set the total days based on the previous month's days. There is additional logic to handle January, which is explicitly set to 31 days.
Calculating Days in Term
Days in Term is the numerator in the Proration Percentage calculation
Prorated Percent = Days in Term / Total Days
Days in Term is calculated by subtracting the Usage Start Date from the Usage End Date and adding one to include both start and end days in the count.
Example: Standard Monthly Billing
In this scenario, the license is billed for a full month without prorating. The usage starts on December 2, 2023, and ends on January 1, 2024.
- Usage Start Date: 2/12/2023
- Usage End Date: 1/01/2024
- Term Start Date: 2/12/2023
- Term End Date: 1/01/2024
Total Days Calculation:
Since the usage spans from December to January, the total days are calculated based on the days in the start month (December), which has 31 days.
Days in Term Calculation:
Days in Term = (1/01/2024 − 2/12/2023) + 1 = 31 days
Prorated Percentage Calculation:
Prorated Percent = 31 / 31 = 1
Since this is a full month without proration, the output is 100%.
Annual Calculations
Based on the outcome of Days in Term, if the stock code starts with "P1Y" and the Days In Term is greater than 31 days we identify the license as Annual and apply a separate set of calculations.
- Recalculates Total Days as the difference between the Term End Date and Term Start Date, adding one to include both dates.
- If the Total Days value is less than 365, we adjust the term to span exactly a year (365 or 366 days depending on leap year)
- Calculates the Prorated Percentage as the ratio of Days in Term to Total Days.
Example: Annual Non-Prorated Billing
In this scenario, the license is billed for a full year without proration. The usage starts on January 12, 2024, and ends on January 11, 2025.
- Usage Start Date: 12/01/2024
- Usage End Date: 11/01/2025
- Term Start Date: 12/01/2024
- Term End Date: 11/01/2025
Total Days Calculation:
The term spans a full year, so the total days are 365.
Days in Term Calculation:
Days in Term = (11/01/2025 − 12/01/2024) + 1 = 365 days
Prorated Percentage Calculation:
Prorated Percent = 365 / 365 = 1
Example: Annual Prorated Billing
In this scenario, the license is billed for a partial year, requiring proration. The usage starts on January 23, 2024, and ends on August 2, 2024.
- Usage Start Date: 23/01/2024
- Usage End Date: 02/08/2024
- Term Start Date: 03/08/2023
- Term End Date: 02/08/2024
Total Days Calculation:
The term spans a full year, so the total days are 365.
Days in Term Calculation:
Days in Term = (02/08/2024 − 23/01/2024) + 1 = 193 days
Prorated Percentage Calculation:
Prorated Percent = 365 / 192 ≈ 0.53
