CreateVendorBalanceDetailReport
Generates a Vendor Balance Detail report schema file, showing individual unpaid bills and credits for each vendor.
Stored Procedure Specific Information
QuickBooks Online allows only a small subset of columns to be used in the Exec query. All the columns are optional for this Stored Procedure.
This report supports multicurrency. The available columns will differ depending on whether multicurrency is enabled or disabled. Below are the columns supported for each state: multicurrency enabled or disabled.
Multicurrency disabled: subt_net_open_bal, rbal_neg_open_bal, subt_neg_amount.
Multicurrency enabled: currency, exch_rate, neg_foreign_open_bal, subt_neg_home_open_bal, rbal_neg_home_open_bal.
To determine if the authenticated account supports multicurrency, check with the following statement:
SELECT CurrencyPrefs_MultiCurrencyEnabled FROM Preferences;
For example:
To create the RSD file, it will be generated based on the value of the Location connection property.
EXECUTE CreateVendorBalanceDetailReport
To return BASE64 encoded data in FileData column.
EXECUTE CreateVendorBalanceDetailReport WriteToFile = 'False'
Input
| Name | Type | Description |
| ReportName | String | The name that is assigned to the vendor balance detail report. This value is used to label and identify the report in generated outputs and downstream integrations.
The default value is VendorBalanceDetailReport. |
| ReportDescription | String | A descriptive summary of the vendor balance detail report that explains its contents or purpose. If you do not specify one, the system automatically generates a description based on the selected report type. |
| AccountingMethod | String | The accounting method that is applied to the report calculations, such as Cash or Accrual. This method determines how income and expenses are recognized in the report.
The allowed values are Cash, Accrual. |
| APPaid | String | Indicates the accounts payable balance status that is used to filter the report contents. This parameter allows you to restrict the report to a specific payable status.
The allowed values are Paid, Unpaid, All. The default value is Unpaid. |
| Columns | String | Specifies the column types that are displayed in the report. Provide a comma separated list (with a space after each comma) of one or more of the following values: create_by, create_date, doc_num, due_date, last_mod_by, last_mod_date, memo, term_name, tx_date, txn_type, vend_bill_addr, vend_comp_name, vend_name, vend_pri_cont, vend_pri_email, vend_pri_tel. The dept_name column is also supported when location tracking is enabled. Based on the multicurrency setting for the company, the following values are also supported. When multicurrency is disabled, the supported values are subt_net_open_bal, rbal_neg_open_bal, and subt_neg_amount. When multicurrency is enabled, the supported values are currency, exch_rate, neg_foreign_open_bal, subt_neg_home_open_bal, and rbal_neg_home_open_bal.
The default value is tx_date,txn_type,doc_num,dept_name,subt_neg_amount,subt_net_open_bal,rbal_neg_open_bal,subt_neg_home_open_bal, rbal_neg_home_open_bal. |
| DateMacro | String | A predefined date range that is automatically applied to the report timeframe. Use this parameter for standard date periods. Otherwise, specify the StartDate and EndDate parameters for a custom range.
The allowed values are Today, Yesterday, This Week, This Week-to-date, Last Week, Last Week-to-date, Next Week, Next 4 Weeks, This Month, This Month-to-date, Last Month, Last Month-to-date, Next Month, This Fiscal Quarter, This Fiscal Year, This Fiscal Year-to-date, Last Fiscal Year, Last Fiscal Year-to-date, Next Fiscal Year. |
| Department | String | Filters the report contents to include transactions for specified departments. Provide a comma separated list (with a space after each comma) of one or more Department identifiers (Ids). |
| DueDateMacro | String | A predefined due date range that is automatically applied to the report timeframe. Use this parameter for standard due date periods. Otherwise, specify the StartDate and EndDate parameters for a custom range.
The allowed values are Today, Yesterday, This Week, This Week-to-date, Last Week, Last Week-to-date, Next Week, Next 4 Weeks, This Month, This Month-to-date, Last Month, Last Month-to-date, Next Month, This Fiscal Quarter, This Fiscal Year, This Fiscal Year-to-date, Last Fiscal Year, Last Fiscal Year-to-date, Next Fiscal Year. |
| ReportDate | String | The ReportDate parameter specifies the start date that is used for the report timeframe. |
| StartDueDate | String | Specifies the starting date of receivables that are due and included in the report. This parameter defines the earliest due date to include and must be earlier than the ending date. |
| EndDueDate | String | Specifies the ending date of the report. This parameter defines the latest due date to include and must be later than the starting date. |
| SortBy | String | Specifies the column type that is used to sort the report rows. Provide one of the column values specified in the Columns input. |
| SortOrder | String | Specifies the sort order that is applied to the report rows, such as ascending or descending.
The allowed values are ascend, descend. The default value is ascend. |
| Term | String | Filters the report contents to include transactions for specified terms. Provide a comma separated list (with a space after each comma) of one or more Term Ids. |
| Vendor | String | Filters the report contents to include transactions for specified vendors. Provide a comma separated list (with a space after each comma) of one or more Vendor Ids. |
| Indentation | String | Indicates whether to add leading spaces to values that are displayed in the report. This field helps visually differentiate groupings and totals within the output for improved readability. |
| WriteToFile | String | Indicates whether the stored procedure writes the output to a file. This input defaults to true. Set it to false to have the procedure write the output to the file stream that you supply or to return the output as file data. |
Result Set Columns
| Name | Type | Description |
| Result | String | Indicates the execution outcome of the stored procedure. Returns a value of Success when the report generation is successful or a value of Failure if it is not. |
| SchemaFile | String | The path or reference to the generated schema file that defines the structure of the returned vendor balance detail report data. |
| Columns | String | The number of columns that are present in the generated report schema. This number provides a quick count of the report's fields for validation or processing. |
| FileData | String | The Base64-encoded content of the generated report file. This output is returned only when the WriteToFile parameter is set to false and the FileStream parameter is not set, enabling you to decode and save the data programmatically. |