Requisition to Vendor Bill Calculations

When a Procore requisition (payment application/invoice) is synchronized to NetSuite, Kurrent.AI transforms the data into one or more NetSuite vendor bills. This document explains the calculations used for each component.

Overview

A single Procore requisition can result in multiple NetSuite transactions:

1. Item Receipt - Records the receipt of goods/services against the purchase order
2. Vendor Bill - The main invoice with work completed, stored materials, and retainage held
3. Vendor Credit - Created when there are negative adjustments
4. Retainage Release Vendor Bill - Created when retainage is released without new work

Line Item Calculations

Each line item in the requisition is processed to calculate the following values.

Work Completed This Period

The amount of work completed during the current billing period.

Source Field: work_completed_this_period

This value is taken directly from the Procore requisition line and represents the dollar amount of work performed.

Stored Materials (Net Change)

Tracks the change in materials stored on-site during the current period.

Calculation:

Stored Materials Net Change = materials_presently_stored - materials_moved

Source Fields:

• materials_presently_stored (or total_materials_presently_stored) - Current value of materials on-site
• materials_moved - Materials that were installed/used this period

Interpretation:

• Positive value = Net increase in stored materials (more materials delivered than installed)
• Negative value = Net decrease in stored materials (more materials installed than delivered)
• Zero = No change in stored materials

Example:

Retainage Held

Retainage is an amount withheld from payment until project completion. Retainage held is aggregated by cost code (WBS code) and appears as negative line items on the vendor bill.

Calculation:

Retainage Held = work_completed_retainage_retained_this_period + total_materials_stored_retainage

Source Fields:

• work_completed_retainage_retained_this_period - Retainage held on work completed
• total_materials_stored_retainage (or materials_stored_retainage_currently_retained) - Retainage held on stored materials

How it appears in NetSuite:

• Retainage is aggregated by cost code (WBS code)
• Creates negative line items on the vendor bill using item ID 2029 (Retainage item)
• Reduces the total vendor bill amount by the retainage being held

Example:

Retainage Released

When previously held retainage is released back to the contractor.

Calculation:

Retainage Released = work_completed_retainage_released_this_period + materials_stored_retainage_released_this_period

Source Fields:

• work_completed_retainage_released_this_period - Retainage released on completed work
• materials_stored_retainage_released_this_period - Retainage released on stored materials

How it appears in NetSuite:

• When a requisition has no work completed but retainage is released, a separate vendor bill is created
• External ID format: VB-RETREL-[requisitionId]
• Creates positive line items using item ID 2029 (Retainage item)
• Aggregated by cost code (WBS code)

When Retainage Release Vendor Bill is Created:

• Work completed this period = $0 (no new work)
• Retainage released this period > $0 (retainage being released)

Aggregation by Cost Code

Both retainage held and retainage released are aggregated by cost code (WBS code) before being written to NetSuite. This means:

1. Multiple requisition lines with the same cost code are combined
2. A single retainage line is created per cost code
3. The line description indicates the cost code: "Retainage for Item Code [costCode]"

Example Aggregation:

Result on Vendor Bill:

Transaction Types Summary

Main Vendor Bill

Created when there is work completed this period.

Contains:

• Positive line items for work completed (referencing PO lines)
• Negative line items for retainage held (aggregated by cost code)
• Custom field custcol_adpi_new_materials_stored for stored materials

External ID: VB-[requisitionId]

Retainage Release Vendor Bill

Created when retainage is released but no new work is performed.

Contains:

• Positive line items for retainage being released
• Aggregated by cost code

External ID: VB-RETREL-[requisitionId]

Vendor Credit

Created when there are negative adjustments (credits).

Contains:

• Positive amounts representing credits back to the company

External ID: VC-[requisitionId]

Field Mapping Reference

Troubleshooting

Retainage Values Don't Match

Check:

1. Verify the source fields in Procore contain the expected values
2. Confirm the cost codes are being resolved correctly
3. Review the aggregation - multiple lines may be combined

Missing Retainage Release Vendor Bill

This only creates when:

• Total work completed = $0
• Retainage released > $0

If work was also completed, the retainage release will be part of the main vendor bill flow.

Stored Materials Showing Unexpected Values

Remember:

• Stored materials is a net change calculation
• Positive = increase in stored materials
• Negative = decrease (materials used)
• Check both materials_presently_stored AND materials_moved values

FAQ

Q: Why is retainage aggregated by cost code? A: NetSuite requires retainage to be tracked at the cost code level for proper job costing and reporting. Aggregating ensures each cost code has a single retainage line.

Q: Can retainage held and released both appear on the same requisition? A: Yes. When work is performed AND retainage is released, both are processed. The main vendor bill handles work completed and new retainage held, while retainage release may create a separate transaction if there's no work.

Q: What happens if a cost code is missing? A: Lines without a cost code are grouped under "DEFAULT" for aggregation purposes.

Q: How do I verify the calculations? A: Use the API Test page at /developer/api-test to call the combined-invoice-data endpoint and see the raw Procore data being used for calculations.