SSIS Components for Zoho Books

Build 24.0.9060

EstimateDetails

List, add, update and delete details of an estimate.

Table Specific Information

Select

The component uses the Zoho Books API to process WHERE clause conditions built with the following column and operator:

  • EstimateId supports the '=' and IN operators.

NOTE: EstimateId is required to query EstimateDetails.

The rest of the filter is executed client-side in the component.

For example: 

    SELECT * FROM EstimateDetails WHERE EstimateId = '1894553000000077244'
	SELECT * FROM EstimateDetails WHERE EstimateId IN (SELECT EstimateId FROM Estimates)
	SELECT * FROM EstimateDetails WHERE EstimateId IN ('1894553000000077244','1894553000000077245')

Insert

INSERT can be executed by specifying the Customerid and lineitems columns. The columns that are not read-only can be inserted optionally. The following is an example of how to insert into this table. 

INSERT INTO EstimateLineItems#TEMP (Name, itemid) VALUES ('Cloth-Jeans1', '3285934000000104097')

INSERT INTO EstimateDetails (Customerid, lineitems) VALUES ('3285934000000104002', EstimateLineItems#Temp)

INSERT can also be executed by specifying the LineItems column as a JSON array. The following is an example of how to insert using JSON array into this table. 

INSERT INTO EstimateDetails (CustomerId, LineItems) VALUES ('3255827000000081003', '[{"Name":"Cloth-Jeans3", "ItemId":"3285934000000104097"}]')

Update

UPDATE can be executed by specifying the EstimateId in the WHERE Clause. The columns that are not read-only can be updated. For example: 

INSERT INTO EstimateLineItems#TEMP (Name, itemid) VALUES ('Cloth-Jeans12', '3285934000000104097')

UPDATE EstimateDetails SET Customerid = '3285934000000104002', lineitems = 'EstimateLineItems#Temp'  WHERE EstimateId = '3285934000000263048'

UPDATE can also be executed by specifying the LineItems column as a JSON array. The following is an example of how to update using JSON array into this table. 

UPDATE EstimateDetails SET CustomerId = '3285934000000085043', LineItems = '[{"Name":"Cloth-Jeans", "ItemId":"3285934000000104097"}]' WHERE EstimateId = '3285934000000263048'

Delete

DELETE can be executed by specifying the EstimateId in the WHERE Clause For example: 

DELETE FROM EstimateDetails WHERE EstimateId = '3285934000000263048'

Columns

Name Type ReadOnly References SupportedOperators Description
EstimateId [KEY] String True

Estimates.EstimateId

Id of an estimate.

Adjustment Decimal False

Adjustments made to the estimate.

AdjustmentDescription String False

Description of adjustments made to the estimate.

AllowPartialPayments Boolean True

Check if estimate allows partial payments.

ApproverId String True

Users.UserId

Id of an approver.

AttachmentName String True

Name of the attachment.

AvataxUseCode String False

Used to group like customers for exemption purposes. It is a custom value that links customers to a tax rule.

AvataxExemptNo String False

Exemption certificate number of the customer.

BcyAdjustment Decimal True

Adjustment made for base currency.

BcyDiscountTotal Decimal True

Total amount get on discount for base currency.

BcyShippingCharge Decimal True

Shipping charge applied for base currency.

BcySubTotal Decimal True

Sub total of base currency.

BcyTaxTotal Decimal True

Total tax applied for the base currency.

BcyTotal Decimal True

Total Base Currency.

BillingAddress String True

Billing address of a estimate.

BillingAddressAttention String True

Name of a person in billing address.

BillingAddressCity String True

City of a billing address.

BillingAddressCountry String True

Country of a billing address.

BillingAddressFax String True

Fax of a billing address.

BillingAddressPhone String True

Phone number of a billing address.

BillingAddressState String True

State of a billing address.

BillingAddressStreet2 String True

Street two of a billing address.

BillingAddressZip String True

ZIP code of a billing address.

CanSendInMail Boolean True

Check if the estimate can be send in mail.

ClientViewedTime Datetime True

Time when client viewed the estimate.

ColorCode String True

Color code for estimate.

ContactPersons String False

Contact persons of a contact.

ContactCategory String True

Category for contact.

CreatedById String True

Users.UserId

Id of a user who has created estimate.

CreatedTime Datetime True

Time at which the estimate was created.

CustomFields String False

Custom fields of the contact.

CurrencyCode String True

Currency code of the customer's currency.

CurrencyId String True

Currencies.CurrencyId

Currency Id of the customer's currency.

CurrencySymbol String True

Currency symbol of the customer's currency.

CurrentSubStatus String True

Current sub status of an estimate .

CurrentSubStatusId String True

Current sub status Id of an estimate .

CustomerDefaultBillingAddress String True

Customer default billing address of an estimate.

CustomerDefaultBillingAddressCity String True

City of a customer default billing address.

CustomerDefaultBillingAddressCountry String True

Country of a customer default billing address.

CustomerDefaultBillingAddressFax String True

Fax of a customer default billing address.

CustomerDefaultBillingAddressPhone String True

Phone number of a customer default billing address.

CustomerDefaultBillingAddressState String True

State of a customer default billing address.

CustomerDefaultBillingAddressStateCode String True

State code of a customer default billing address.

CustomerDefaultBillingAddressStreet2 String True

Street two of a customer default billing address.

CustomerDefaultBillingAddressZip String True

ZIP code of a customer default billing address.

CustomerId String False

Contacts.ContactId

Id of the customer or vendor.

CustomerName String True

Name of the customer or vendor.

Date Date False

Date of an estimate.

Discount String False

Discount applied for estimate.

DiscountAppliedOnAmount Decimal True

Discount applied on amount for estimate.

DiscountPercent Decimal True

Percent of discount applied for estimate.

DiscountTotal Decimal True

Total discount applied for estimate.

DiscountType String False

Type of discount applied for estimate.

EstimateNumber String False

Number of estimate.

EstimateUrl String True

URL of estimate.

ExchangeRate Decimal False

Exchange rate of the currency.

ExpiryDate Date False

Expiration date of estimate.

InvoiceConversionType String True

Conversion type of an invoice in estimate.

IsConvertedToOpen Boolean True

Check if the estimate is converted to open.

IsDiscountBeforeTax Boolean False

Check if the discount is applied before tax. .

IsInclusiveTax Boolean False

Check if the expense is inclusive tax.

IsPreGst Boolean True

Check if estimate includes pre GST.

IsTransactionCreated Boolean True

Check if the transaction os created for estimate.

IsViewedByClient Boolean True

Check if the estimate is viewed by client.

LastModifiedById String True

Users.UserId

Id of the user last modified.

LastModifiedTime Datetime True

The time of last modification of the estimate.

LineItems String False

Line items of an estimate.

Notes String False

Notes of Estimate.

Orientation String True

Orientation of page.

PageHeight String True

Height of page.

PageWidth String True

Width of page.

PricePrecision Integer True

The precision for the price.

ProjectId String False

Id of a project.

ProjectName String True

Name of a project.

ProjectCustomerId String True

Id of a customer.

ProjectCustomerName String True

Name of a customer.

ProjectDescription String True

Details about the project.

ProjectStatus String True

Status of the project.

ProjectBillingType String True

Type of billing.

ProjectRate Decimal True

Overall rate of the project.

ReferenceNumber String False

Reference number of estimates.

PlaceOfSupply String False

The place of supply is where a transaction is considered to have occurred for VAT purposes.

TaxSpecification String True

Working of tax when specifying special tax options and tax methods for earnings codes.

VatTreatment String False

VAT treatment for the estimates.

IsTaxable Boolean True

Check if estimate is taxble.

GstNo String False

GST number.

GstTreatment String False

Choose whether the estimate is GST registered/unregistered/consumer/overseas. .

TaxTreatment String False

VAT treatment for the Estimate.

ReverseChargeTaxTotal String True

Total amount of tax reverse charge.

CanSendEstimateSms String True

Check if the estimate can send the estimate SMS.

RetainerPercentage String True

Percentage of the retainer in estimate.

AcceptRetainer Boolean True

Check if estimate can accept the retainer.

RoundoffValue Decimal True

Rounding off the values to precise number.

SalespersonId String False

Id of a sales person.

SalespersonName String False

Name of a sales person.

ShippingAddress String True

Shipment Address.

ShippingAddressAttention String True

Name of a person of shipping address.

ShippingAddressCity String True

City of a shipping address.

ShippingAddressCountry String True

Country of a shipping address.

ShippingAddressFax String True

Fax of a shipping address.

ShippingAddressPhone String True

Phone number of a shipping address.

ShippingAddressState String True

State of a shipping address.

ShippingAddressStreet2 String True

Street two of a shipping address.

ShippingAddressZip String True

Zip of a shipping address.

ShippingCharge Decimal False

Shipping charge of estimates.

Status String True

Status of the estimate.

SubTotal Decimal True

Sub total of estimates.

SubTotalExclusiveOfDiscount Decimal True

Subtotal amount which are exclusive of discount.

SubTotalInclusiveOfTax Decimal True

Subtotal amount which are inclusive of tax.

SubmittedBy String True

Detail of the user who has submitted the estimate.

SubmittedDate Date True

Date when estimate was submitted.

SubmitterId String True

Users.UserId

Id of the submitter.

TaxId String False

Taxes.TaxId

Id of the tax.

TaxTotal Decimal True

Total amount of tax.

TemplateId String False

Id of a template.

TemplateName String True

Name of a template.

TemplateType String True

Type of a template.

Terms String False

Terms and Conditions apply of a estimate.

Total Decimal True

Total of estimates.

TransactionRoundingType String True

Type of round off used for transaction.

Unit String False

Unit of the line item e.g. kgs, Nos.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
Send Boolean

Send the invoice to the contact person(s) associated with the invoice. Allowed values true and false.

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.9060