Owner Billing with Invoice Codes Report Mapping
You can map DaaS data to the Textura Owner Billing with Invoice Codes Report with the following prompt or query.
Suggested Prompt to use in Textura Reports
Get budget lines for my organization that:
- Are not deleted
- Where the organization’s role is CHILD
For each budget line, include:
Budget totals (Self)
- Original budget amount
- Change order amount
- Contract sum to date
- Billed to date
- Retention amount to date
- Retention released to date
- Balance remaining
- Percent complete
Budget totals (Subcontracted)
- Original budget amount
- Change order amount
- Contract sum to date
- Billed to date
- Retention amount to date
- Retention released to date
- Balance remaining
- Percent complete
Budget line details
- Budget line ID
- Date modified
- Whether it is deleted
Contract information
For each budget line, include:
- Contract ID
Invoices under the contract
For each invoice, include:
- Invoice summary
- Invoice ID
- Invoice number
- System-generated invoice number
- Invoice status
- Net invoice amount
- Net payment due
- Tax amount
- Discount and discount expiration date
- Whether the invoice is deleted
- Date modified
Self amounts (this period)
- Work this period
- Materials stored this period
- Billed amount this period
- Retention amount this period
- Retention released this period
Subcontracted amounts (this period)
- Work this period
- Materials stored this period
- Billed amount this period
- Retention amount this period
- Retention released this period
Invoice line details
For each invoice line, include:
Self amounts
- Work this period
- Materials stored this period
- Retention amount this period
- Retention released this period
Subcontracted amounts
- Work this period
- Materials stored this period
- Retention amount this period
- Retention released this period
Coding details
- Parent phase code and description
- Phase code and description
- Parent invoice code and description
- Invoice code and description
- Parent breakout code and description
- Breakout code and description
- Parent handling code and description
- Handling code and description
Draw information (for each invoice)
- Draw ID
- Draw number
Project information (for each budget line)
- Project ID
- Project name
- Project number
- Segment ID
Note:
- For more information on using prompts, see topic Creating a Report from a Prompt.
- The above prompt is a suggestion, your results may vary.
Query
query BudgetLines {
budgetLines(
offset: 0
next: 1000
organizationID: XXX
isDeleted: false
orgRole: CHILD
) {
id
selfOriginalBudgetAmount
selfChangeOrderAmount
selfContractSumToDate
selfBilledToDate
selfRetentionAmountToDate
selfRetentionReleasedToDate
selfBalanceRemaining
selfPercentComplete
subcontractedOriginalBudgetAmount
subcontractedChangeOrderAmount
subcontractedContractSumToDate
subcontractedBilledToDate
subcontractedRetentionAmountToDate
subcontractedRetentionReleasedToDate
subcontractedBalanceRemaining
subcontractedPercentComplete
dateModified
isDeleted
contract {
id
invoices {
id
invoiceNumber
systemGeneratedInvoiceNumber
selfWorkThisPeriod
selfMaterialStoredThisPeriod
selfBilledAmountThisPeriod
selfRetentionAmountThisPeriod
selfRetentionReleasedThisPeriod
subcontractedWorkThisPeriod
subcontractedMaterialStoredThisPeriod
subcontractedBilledAmountThisPeriod
subcontractedRetentionAmountThisPeriod
subcontractedRetentionReleasedThisPeriod
taxAmount
invoiceStatus
netPaymentDue
netInvoiceAmount
discount
discountExpirationDate
isDeleted
dateModified
invoiceLines {
id
selfWorkThisPeriod
selfMaterialStoredThisPeriod
selfRetentionAmountThisPeriod
selfRetentionReleasedThisPeriod
subcontractedWorkThisPeriod
subcontractedMaterialStoredThisPeriod
subcontractedRetentionAmountThisPeriod
subcontractedRetentionReleasedThisPeriod
parentPhaseCode
parentPhaseCodeDescription
phaseCode
phaseCodeDescription
parentInvoiceCode
parentInvoiceCodeDescription
invoiceCode
invoiceCodeDescription
parentBreakoutCode
parentBreakoutCodeDescription
breakoutCode
breakoutCodeDescription
parentHandlingCode
parentHandlingCodeDescription
handlingCode
handlingCodeDescription
}
draw {
id
number
}
}
project {
id
name
projectNumber
segmentID
}
}
}
}Mapping Table
Note: If you copy text from a table cell for use in code, paste it as plain text to avoid copying typographic quotation marks or other formatting.
Use the table below to map report output column fields to DaaS fields. Note that cells with a DaaS Field of Not Applicable do not have a DaaS equivalent to an available report field.
| Column Number | Report Column | DaaS Field | Notes |
|---|---|---|---|
| 1 | SegmentID | Budget Lines.Contract.Project.segmentID | |
| 2 | Project Name | Budget Lines.Contract.Project.name | |
| 3 | Project Number | Budget Lines.Contract.Project.projectNumber | |
| 4 | Draw Number | Budget Lines.Contract.Invoice.Draw.number | |
| 5 | Invoice Code | budgetLines.contract.invoice.invoiceLine.invoiceCode | |
| 6 | Invoice Code Description | budgetLines.contract.invoice.invoiceLine.invoiceCodeDescription | |
| 7 | Breakout Code | budgetLines.contract.invoice.invoiceLine.breakoutCode | |
| 8 | Breakout Code Description | budgetLines.contract.invoice.invoiceLine.breakoutCodeDescription | |
| 9 | Handling Code | budgetLines.contract.invoice.invoiceLine.handlingCode | |
| 10 | Handling Code Description | budgetLines.contract.invoice.invoiceLine.handlingCodeDescription | |
| 11 | Phase Code | budgetLines.contract.invoice.invoiceLine.phaseCode | |
| 12 | Description | budgetLines.contract.invoice.invoiceLine.phaseCodeDescription | |
| 13 | Scheduled Value | budgetLines.selfContractSumToDate + budgetLines.subcontractedContractSumToDate | |
| 14 | Work Completed Previous | budgetLines.contract.invoice.invoiceLine.selfWorkThisPeriod + budgetLines.contract.invoice.invoiceLine.selfMaterialStoredThisPeriod + budgetLines.contract.invoice.invoiceLine.subcontractedWorkThisPeriod + budgetLines.contract.invoice.invoiceLine.subcontractedMaterialStoredThisPeriod | Sum across all draws except the latest draw |
| 15 | Work Completed this Period | budgetLines.contract.invoice.invoiceLine.selfWorkThisPeriod + budgetLines.contract.invoice.invoiceLine.subcontractedWorkThisPeriod | For the latest draw corresponding to each budget line. |
| 16 | Materials Stored This Period | budgetLines.contract.invoice.invoiceLine.selfMaterialStoredThisPeriod + budgetLines.contract.invoice.invoiceLine.subcontractedMaterialStoredThisPeriod | For the latest draw corresponding to each budget line. |
| 17 | Total Completed and Stored to Date | budgetLines.contract.invoice.invoiceLine.selfWorkThisPeriod + budgetLines.contract.invoice.invoiceLine.selfMaterialStoredThisPeriod + budgetLines.contract.invoice.invoiceLine.subcontractedWorkThisPeriod + budgetLines.contract.invoice.invoiceLine.subcontractedMaterialStoredThisPeriod | Sum of all Draws |
| 18 | Percent Complete | [Total Completed and Stored to Date] * 100 / [Scheduled Value] | Refer the fields and mapping table to get the calculations for “Total Completed and Stored to Date” and “Scheduled Value”. |
| 19 | Balance to Finish | budgetLines.selfBalanceRemaining + budgetLInes.subcontractedBalanceRemaining | |
| 20 | Retention Held this Period | budgetLines.contract.invoice.invoiceLine.selfRetentionAmountThisPeriod + budgetLines.contract.invoice.invoiceLine.subcontractedRetentionAmountThisPeriod | For the latest draw corresponding to each budget line. |
| 21 | Retention % this Period | Not Applicable | |
| 22 | Retention to Date Amount | budgetLines.selfRetentionAmountToDate + budgetLines.subcontractedRetentionAmountToDate | |
| 23 | Retention Request Previously Held | budgetLines.selfRetentionReleasedToDate + budgetLines.subcontractedRetentionReleasedToDate |