TIBCO Scribe® Online Connector For Sage 300
Use the TIBCO Scribe® Online Connector for Sage 300 to access your on-premise or cloud environment through OData Web Services. This Connector exposes each action, object, and property for a single module. Create separate connections for each module you want to access.
Connector Specifications
This Connector supports Sage 300 and is compliant with OData version 4.
Supported | |
---|---|
Agent Types |
|
On Premise | X |
Cloud | X |
Replication Services |
|
Source | |
Target | |
Integration Services |
|
Source | X |
Target | X |
Migration Services |
|
Source | X |
Target | X |
Maps |
|
Integration | X |
Request-Reply | X |
Message |
Note: This Connector is available from the TIBCO Scribe® Online Marketplace. See Marketplace TIBCO Scribe® Certified Connectors for more information.
Supported Entities/Operations
Sage 300 Connections support one module each. Separate metadata is loaded for each module and does not contain any indication of which operations are supported for each entity. Therefore, each operation block displays all entities for the Connection, but some entities may not actually support the operation. For example, the AP1099Accounts entity only supports Query, but it displays as an option in all operation blocks. Refer to the Sage 300 API documentation for detailed information on each entity and the operations supported.
Supported Entities And Operations By Module
More modules and entities could be added to the Sage 300 API at any time and display in TIBCO Scribe® Online. Modules included in this document are as follows:
AP
Entity | Query | Create With | Update PatchWith | Update ReplaceWith | Delete |
---|---|---|---|---|---|
AP1099CPRSAmounts |
X |
|
|
|
|
AP1099CPRSCodes |
X |
X |
X |
X |
X |
APAccountSets |
X |
X |
X |
X |
X |
APCreateGLBatch |
|
X |
|
|
|
APCreateRecurringPayableBatch |
|
X |
|
|
|
APCreateRetainageDocumentBatch |
|
X |
|
|
|
APDeleteInactiveRecords |
|
X |
|
|
|
APDistributionCodes |
X |
X |
X |
X |
X |
APDistributionSets |
X |
X |
X |
X |
X |
APInvoiceBatches |
X |
X |
|
|
|
APPaymentAndAdjustmentBatches |
X |
X |
|
|
|
APPaymentCodes |
X |
X |
X |
X |
X |
APPostedDocuments |
X |
|
|
|
|
APPostedPayments |
X |
|
|
|
|
APPostingErrorMessages |
X |
|
|
|
|
APPostInvoices |
|
X |
|
|
|
APPostPaymentsAndAdjustments |
|
X |
|
|
|
APRecurringPayables |
X |
X |
X |
X |
X |
APRemitToLocations |
X |
X |
X |
X |
X |
APTerms |
X |
X |
X |
X |
X |
APVendorGroups |
X |
X |
X |
X |
X |
APVendors |
X |
X |
X |
X |
X |
APVendorStatistics |
X |
|
|
|
|
AR
Entity | Query | Create With | Update PatchWith | Update ReplaceWith | Delete |
---|---|---|---|---|---|
ARAccountSets |
X |
X |
X |
X |
X |
ARBillingCycles |
X |
X |
X |
X |
X |
ARCommentTypes |
X |
X |
X |
X |
X |
ARCreateGLBatch |
|
X |
|
|
|
ARCreateRecurringCharge |
|
X |
|
|
|
ARCreateRetainageDocumentBatch |
|
X |
|
|
|
ARCustomerGroups |
X |
X |
X |
X |
X |
ARCustomers |
X |
X |
X |
X |
X |
ARDistributionCodes |
X |
X |
X |
X |
X |
ARDunningMessages |
X |
X |
X |
X |
X |
ARInvoiceBatches |
X |
X |
|
|
|
ARItems |
X |
X |
X |
X |
X |
ARItemStatistics |
X |
|
|
|
|
ARNationalAccounts |
X |
X |
X |
X |
X |
ARNationalAccountStatistics |
X |
|
|
|
|
ARPaymentCodes |
X |
X |
X |
X |
X |
ARPayments |
X |
|
|
|
|
ARPostedDocuments |
X |
|
|
|
|
ARPostedReceipts |
X |
|
|
|
|
ARPostInvoices |
|
X |
|
|
|
ARPostReceiptsAndAdjustments |
|
X |
|
|
|
ARPostRefunds |
|
X |
|
|
|
ARReceiptAndAdjustmentBatches |
X |
X |
|
|
|
ARRecurringCharges |
X |
X |
X |
X |
X |
ARRefundBatches |
X |
X |
|
|
|
ARSalespersons |
X |
X |
X |
X |
X |
ARSalespersonStatistics |
X |
|
|
|
|
ARShipToLocations |
X |
X |
X |
X |
X |
ARTerms |
X |
X |
X |
X |
X |
BK
Entity | Query | Create With | Update PatchWith | Update ReplaceWith | Delete |
---|---|---|---|---|---|
BKBankDistributionCodes |
X |
X |
X |
X |
X |
BKBankEntries |
X |
X |
|
|
|
BKBanks |
X |
X |
X |
X |
X |
BKCreditCardTypes |
X |
X |
X |
X |
X |
CS
Entity | Query | Create With | Update PatchWith | Update ReplaceWith | Delete |
---|---|---|---|---|---|
CSCompanies |
X |
|
|
|
|
CSCompanyProfile |
X |
|
X |
X |
|
CSCurrencyCodes |
X |
X |
X |
X |
X |
CSCurrencyRates |
X |
X |
X |
X |
X |
CSCurrencyRateTypes |
X |
X |
X |
X |
X |
CSFiscalCalendars |
X |
|
|
|
|
CSSchedules |
X |
X |
X |
X |
X |
CSUserData |
X |
X |
|
X |
X |
GL
Entity | Query | Create With | Update PatchWith | Update ReplaceWith | Delete |
---|---|---|---|---|---|
GLAccountFiscalSets |
X |
|
|
|
|
GLAccountGroups |
X |
X |
X |
X |
X |
GLAccounts |
X |
X |
X |
X |
X |
GLJournalBatches |
X |
X |
|
|
|
GLPostedTransactions |
X |
|
|
|
|
GLPostingJournalDetails |
X |
|
|
|
|
GLPostJournal |
|
X |
|
|
|
GLProvisionalPostingErrors |
X |
|
|
|
|
GLRevaluationCodes |
X |
X |
X |
X |
X |
GLSegmentCodes |
X |
X |
X |
X |
X |
GLSourceCodes |
X |
X |
X |
X |
X |
IC
Entity | Query | Create With | Update PatchWith | Update ReplaceWith | Delete |
---|---|---|---|---|---|
ICAccountSets |
X |
X |
X |
X |
X |
ICAdjustments |
X |
X |
|
|
|
ICBillsOfMaterial |
X |
|
|
|
|
ICCategories |
X |
X |
X |
X |
X |
ICCreateGLBatch |
|
X |
|
|
|
ICDayEndProcessing |
|
X |
|
|
|
ICItems |
X |
X |
X |
X |
X |
ICItemStructures |
X |
X |
X |
X |
X |
ICLocations |
X |
X |
X |
X |
X |
ICPriceListCodes |
X |
X |
X |
X |
X |
ICReceipts |
X |
X |
|
|
|
ICShipments |
X |
X |
|
|
|
ICTransfers |
X |
X |
|
|
|
ICUnitsOfMeasure |
X |
X |
X |
X |
X |
ICWeightUnitsOfMeasure |
X |
X |
X |
X |
X |
MT
Entity | Query | Create With | Update PatchWith | Update ReplaceWith | Delete |
---|---|---|---|---|---|
MTContactForms |
X |
X |
|
|
|
MTContacts |
X |
X |
X |
X |
X |
OE
Entity | Query | Create With | Update PatchWith | Update ReplaceWith | Delete |
---|---|---|---|---|---|
OECreditDebitNotes |
X |
X |
|
|
|
OEInvoices |
X |
|
|
|
|
OEMiscellaneousCharges |
X |
X |
X |
X |
X |
OEOrders |
X |
X |
|
|
|
OEPostingJournals |
X |
|
|
|
|
OESalesHistory |
X |
|
|
|
|
OESalesHistoryDetails |
X |
|
|
|
|
OESalesStatistics |
X |
|
|
|
|
OEShipViaCodes |
X |
X |
X |
X |
X |
OETemplates |
X |
X |
X |
X |
X |
PO
Entity | Query | Create With | Update PatchWith | Update ReplaceWith | Delete |
---|---|---|---|---|---|
POAccountSets |
X |
X |
X |
X |
X |
POAdditionalCosts |
X |
X |
X |
X |
X |
POCreateBatch |
|
X |
|
|
|
POCreditDebitNotes |
X |
|
|
|
|
PODayEndProcessing |
|
X |
|
|
|
POInvoices |
X |
|
|
|
|
POPurchaseOrders |
X |
X |
|
|
|
POReceipts |
X |
X |
|
|
|
PORequisitions |
X |
X |
|
|
|
POReturns |
X |
X |
|
|
|
POTemplates |
X |
X |
X |
X |
X |
POVendorContractCosts |
X |
X |
X |
X |
X |
TX
Entity | Query | Create With | Update PatchWith | Update ReplaceWith | Delete |
---|---|---|---|---|---|
TXTaxAuthorities |
X |
|
|
|
|
TXTaxGroups |
X |
|
|
|
|
TXTaxRates |
X |
|
|
|
|
TXTaxTracking |
X |
|
|
|
|
Standard Operations
Standard operations for Sage 300 entities include:
- Query
- Delete
Special Operations
- CreateWith —
- Container Block used with hierarchical data to add a parent record that may contain one or more child records. This Block collects all of the data for a single record before writing the entire record to the target. See CreateWith Block.
- Numeric enumeration values require quotes when used in the CreateWith Block Properties Fields tab to map a value to a target field. If the value is not in quotes, an error similar to the following is generated:
Operation failed. Label: Create ARCommentTypes, Name: ARCommentTypesCreate, Message: Error Code: 422 Description: { "error": { "code": "InvalidPayload", "message":
{ "lang": "en-US", "value": "The payload is invalid for this resource.Cannot read the value '10' as a quoted JSON string value." }
- Update Patch With —
- Container Block based on the UpdateWith Block used with hierarchical data. Use this Block to update a parent record that may contain one or more child records. This Block collects all of the data for a single parent record before writing the record to the target.
- Any properties not included in the request are not altered.
- Any field containing a NULL value or an empty string overwrites any value previously in that field.
Note: Operation results from an UpdateWith Block are not available for use in subsequent Blocks.
- Update Replace With —
- Container Block based on the UpdateWith Block used with hierarchical data. Use this Block to replace a parent record that may contain one or more child records. This Block collects all of the data for a single parent record before writing the record to the target.
- Update Replace With overwrites the entire record. If any mapped source fields are empty, target fields with data are overwritten. Any data in fields with unsupported data types is lost.
Note: Operation results from an UpdateWith Block are not available for use in subsequent Blocks.
- Add — Used for hierarchical data to add child records to the parent selected in a CreateWith or UpdateWith Block. See Add Block.
Setup Considerations
Entities
You must have a separate Connection to Sage 300 for each module you want to access, such as AR - Accounts Receivable or AP - Accounts Payable. Once the Connection has been configured, the entities included in that module can be accessed by TIBCO Scribe® Online.
User
Users must have Web API rights in Sage 300 for each module they want to access.
Selecting An Agent Type For Sage 300
Refer to TIBCO Scribe® Online Agents for information on available Agent types and how to select the best Agent for your Solution.
Connecting To Sage 300
Note: Best practice is to create Connections with credentials that limit permissions in the target system, following the principle of least privilege. Using Administrator level credentials in a Connection provides Administrator level access to the target system for TIBCO Scribe® Online users. Depending on the entities supported, a TIBCO Scribe® Online user could alter user accounts in the target system.
- Select More > Connections from the menu.
- From the Connections page select Add to open the Add a New Connection dialog.
- Select the Connector from
the drop-down list in the Connection Type field, and then enter the following information for this Connection:
- Name — This can be any meaningful name, up to 25 characters.
- Alias — An alias for this Connection name. The alias is generated from the Connection name, and can be up to 25 characters. The Connection alias can include letters, numbers, and underscores. Spaces and special characters are not accepted. You can change the alias. For more information, see Connection Alias.
- OData Service URL — Address of any acceptable URL where the Sage 300 OData service is located.
Note: Only one module can be specified in the OData Service URL. Configure a new Connection for each module you need to access, such as Accounts Receivable (AR) or Accounts Payable (AP).
Example: http://sage300/Sage300WebApi/v1.0/-/<yourcompany>/AR
- User — Name of the user with rights to access the Sage 300 OData service .
- Password — Password for User, if your Sage 300 OData service requires authentication.
For more information on OData, see your endpoint provider.
- Select Test to ensure that the Agent can connect to your database. Be sure to test the Connection against all Agents that use this Connection. See Testing Connections.
- Select OK/Save to save the Connection.
Metadata Notes
- Supports Sage 300 Collections, also referred to as Hierarchical data.
- Supports Sage 300 Complex Types.
- DateTime fields only contain date values.
OData Metadata
Only supported OData data types appear in TIBCO Scribe® Online. The following data types are not supported by this Connector:
- Edm.Geography
- Edm.GeographyPoint
- Edm.GeographyLineString
- Edm.GeographyPolygon
- Edm.GeographyMultiPoint
- Edm.GeographyMultiLineString
- Edm.GeographyMultiPolygon
- Edm.GeographyCollection
- Edm.Geometry
- Edm.GeometryPoint
- Edm.GeometryLineString
- Edm.GeometryPolygon
- Edm.GeometryMultiPoint
- Edm.GeometryMultiLineString
- Edm.GeometryMultiPolygon
- Edm.GeometryCollection
- Edm.Stream
Naming
Connection metadata must have unique entity, relationship, and field names. If your Connection metadata has duplicate names, review the source system to determine if the duplicates can be renamed.
Relationships
- Using relationships in the Include tab on the Query Block or Fetch Block may require you to expose related entities and fields to the OData web services in Sage 300.
- Hierarchical relationships, such as grandparent, parent, grandchild relationships are supported. See Hierarchical Data for examples.
Retry Logic
When a timeout occurs, the Connector retries three times with the following delays in between:
- Retry 1 delay = 200 milliseconds
- Retry 2 delay = 400 milliseconds
- Retry 3 delay = 600 milliseconds
Sage 300 Connector As IS Source
Consider the following when using the Sage 300 Connector as an IS source.
Filtering
Supports Net Change.
When a datetime is configured on the Query Block on the Block Properties Net Change Tab to query for new and updated records, that configuration is treated as an additional filter. The Net Change datetime filter is applied as an AND after any other filters specified on the Block Properties Filter Tab. TIBCO Scribe® Online builds a query combining both the Net Change filter and the filters on the Filter tab. See Net Change And Filters for an example.
Some Connectors for TIBCO Scribe® Online only support one filter. For those Connectors you can use either Net Change or one filter on the Filter tab, not both.
Note: The Net Change date is ignored when previewing data on the Preview tab. Filters on the Block Properties Filters tab are used to filter the data on the Preview tab.
Net Change And DateTime Fields
Net Change uses DateTime fields to locate new and updated records and stores the DateTime value of the last record processed. The next time the Solution runs, the Query Block requests records with a DateTime that is greater than the DateTime value of the last record processed. Sage 300 only stores the date value, but not the time. When using Net Change with date only fields, running a Solution another time on the same day causes new and updated records for that day to be skipped. For example, if you run a Solution on 10/15/2020 at 10 AM and the last record processed has a date of 10/15/2020, when the Solution runs again on 10/15/2020 at 9 PM, any records updated or created between 10 AM and 9 PM are skipped because Net Change is requesting records with a date greater than 10/15/2020.
Best practice is to schedule the Solution to run once a day after all records have been created or modified for the day to process all of the records for that day. For example, if your business day ends at 6 PM, schedule the Solution to run after 6 PM to ensure all records for the day are processed. Another option is to disable Net Change and configure a filter on the Filters tab.
Sage 300 Connector As IS Target
Consider the following when using the Sage 300 Connector as an IS target.
Batch processing is not supported.
TIBCO Scribe® Online API Considerations
To create connections with the TIBCO Scribe® Online API, the Sage 300 Connector requires the following information:
Connector Name |
Sage 300 |
Connector ID |
2310AC6C-8065-4D86-88AC-1BC70FBF49BE |
TIBCO Scribe® Online Connection Properties
In addition, this Connector uses the Connection properties shown in the following table.
Note: Connection property names are case-sensitive.
Name | Data Type | Required | Secured | Usage |
---|---|---|---|---|
ServiceUrl |
String |
Yes |
No |
|
User |
String |
Yes |
No |
|
Password |
String |
Yes |
Yes |
|
License Agreement
The TIBCO Scribe® Online End User License Agreement for the Sage 300 Connector describes TIBCO and your legal obligations and requirements. TIBCO suggests that you read the End User License Agreement.
More Information
For additional information on this Connector, refer to the Knowledge Base and Discussions in the TIBCO Community.