PayPal Connection Details¶
Introduction¶
Connector Version
This documentation is based on version 23.0.8804 of the connector.
Get Started¶
PayPal Version Support
The connector uses the PayPal SOAP API to surface views of transactions and the REST API to surface all other views.
Establish a Connection¶
Connect to PayPal¶
The provider surfaces tables from two PayPal APIs. Each API uses a different authentication method.
- The SOAP API requires Signature API credentials. To authenticate to the SOAP API, you must set the
AuthScheme
toBasic
, and set theUsername
,Password
, andSignature
properties. These API credentials are generated when you enable the SOAP API. - The REST API uses the OAuth authentication standard. To authenticate to the REST API, you must set the
AuthScheme
toOAuth
, and set theOAuthClientId
, andOAuthClientSecret
properties.
To specify your preferred API, set the Schema
property to either REST
or SOAP
. If no Schema
is set, PayPal uses the SOAP schema.
For testing purposes, set UseSandbox
to true and use Sandbox credentials.
SOAP API¶
You can use the SOAP API to retrieve all transactions for the current account. The SOAP API uses the Signature API-credentials type, which has three credential values:
- API Username
- API Password
- API Signature
To generate the API credentials:
- Log in to your PayPal business account and select
Profile > My Selling Tools
. - In the Selling Online section, click
Update
for the API Access item. - To generate the API signature, click
Request API Credentials
on the API Access page. - Select
Request API Signature
and clickAgree and Submit
to generate the API signature.
After setting the following connection properties, you are ready to connect:
User
= the API Username.Password
= the API Password.Signature
= the Signature.UseSandbox
=true
if you are using sandbox credentials.
REST API¶
After setting the following connection properties, you are ready to connect:
InitiateOAuth
=GETANDREFRESH
. You can useInitiateOAuth
to avoid repeating the OAuth exchange and manually setting theOAuthAccessToken
connection property.OAuthClientId
= the Client ID in your app settings.OAuthClientSecret
= the Client Secret in your app settings.UseSandbox
=true
if you are using sandbox credentials.
When you connect, the connector completes the OAuth process.
- Exchanges the
OAuthClientId
andOAuthClientSecret
for theOAuthAccessToken
. - Refreshes the access token when it expires.
- Saves OAuth values in
OAuthSettingsLocation
to be persisted across connections.
PayPal supports only OAuth authentication for use with the REST API. To enable this authentication from all OAuth flows, you must set AuthScheme
to OAuth
, and you must create a custom OAuth application.
The following subsections describe how to authenticate to PayPal from three common authentication flows. For information about how to create a custom OAuth application, see Creating a Custom OAuth Application. For a complete list of connection string properties available in PayPal, see Connection.
Desktop Applications¶
To authenticate with the credentials for a custom OAuth application, you must get and refresh the OAuth access token. After you do that, you are ready to connect.
Get and refresh the OAuth access token:
InitiateOAuth
=GETANDREFRESH
. Used to automatically get and refresh theOAuthAccessToken
.OAuthClientId
= the client ID assigned when you registered your application.OAuthClientSecret
= the client secret that was assigned when you registered your application.CallbackURL
= the redirect URI that was defined when you registered your application.
When you connect, the connector opens PayPal's OAuth endpoint in your default browser. Log in and grant permissions to the application.
After you grant permissions to the application, the connector then completes the OAuth process:
- The connector obtains an access token from PayPal and uses it to request data.
- The OAuth values are saved in the path specified in
OAuthSettingsLocation
. These values persist across connections.
When the access token expires, the connector refreshes it automatically.
Automatic refresh of the OAuth access token:
To have the connector automatically refresh the OAuth access token, do the following:
- Before connecting to data for the first time, set the following connection parameters:
InitiateOAuth
=REFRESH
.OAuthClientId
= the client ID in your application settings.OAuthClientSecret
= the client secret in your application settings.OAuthAccessToken
= the access token returned by GetOAuthAccessToken.OAuthSettingsLocation
= the path where you want the connector to save the OAuth values, which persist across connections.
- On subsequent data connections, set the following:
InitiateOAuth
OAuthSettingsLocation
Manual refresh of the OAuth access token:
The only value needed to manually refresh the OAuth access token is the OAuth refresh token.
- To manually refresh the OAuthAccessToken after the ExpiresIn period (returned by GetOAuthAccessToken) has elapsed, call the RefreshOAuthAccessToken stored procedure.
- Set the following connection properties:
OAuthClientId
= the Client ID in your application settings.OAuthClientSecret
= the Client Secret in your application settings.
- Call RefreshOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken.
- After the new tokens have been retrieved, set the
OAuthAccessToken
property to the value returned by RefreshOAuthAccessToken. This opens a new connection.
Store the OAuth refresh token so that you can use it to manually refresh the OAuth access token after it has expired.
When you are finished, configure the driver to automatically refresh the access token on the headless machine.
Create a Custom OAuth Application¶
Create a Custom OAuth Application¶
A custom OAuth application is required to authenticate on Desktop applications, Web applications, or Headless machines while using the REST API.
To register an OAuth application and obtain the OAuth client credentials, the Client Id, and Client Secret:
- Log in to your PayPal developer account.
- Navigate to
My Apps & Credentials
(under Dashboard). - Scroll to the REST API apps section.
- Click
Create App
. - Specify a name for your application.
- From the drop down list, select the sandbox account from which data will be retrieved.
PayPal generates a set of sandbox and live OAuth keys for the application. The sandbox credentials display after the application has been created. To see the live credentials, select the Live
option.
The sandbox credentials are used to get data only for the sandbox account you selected while creating the application. The Live credentials are used to get data only for the developer account that was used to create this application.
Important Notes¶
Configuration Files and Their Paths¶
- All references to adding configuration files and their paths refer to files and locations on the Jitterbit agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.
Advanced Features¶
This section details a selection of advanced features of the PayPal connector.
User Defined Views
The connector allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.
SSL Configuration
Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert
property under "Connection String Options" for more information.
Proxy
To configure the connector using private agent proxy settings, select the Use Proxy Settings
checkbox on the connection configuration screen.
Query Processing
The connector offloads as much of the SELECT statement processing as possible to PayPal and then processes the rest of the query in memory (client-side).
User Defined Views¶
The PayPal connector allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from Jitterbit. The User Defined Views can be used to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.
There are two ways to create user defined views:
- Create a JSON-formatted configuration file defining the views you want.
- DDL statements.
Define Views Using a Configuration File¶
User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json
. The connector automatically detects the views specified in this file.
You can also have multiple view definitions and control them using the UserDefinedViews
connection property. When you use this property, only the specified views are seen by the connector.
This User Defined View configuration file is formatted as follows:
- Each root element defines the name of a view.
- Each root element contains a child element, called
query
, which contains the custom SQL query for the view.
For example:
{
"MyView": {
"query": "SELECT * FROM Payments WHERE MyColumn = 'value'"
},
"MyView2": {
"query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
}
}
Use the UserDefinedViews
connection property to specify the location of your JSON configuration file. For example:
"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"
Define Views Using DDL Statements¶
The connector is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.
Create a View¶
To create a new view using DDL statements, provide the view name and query as follows:
CREATE LOCAL VIEW [MyViewName] AS SELECT * FROM Customers LIMIT 20;
If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews
connection property.
Alter a View¶
To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead:
ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';
The view is then updated in the JSON configuration file.
Drop a View¶
To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.
DROP LOCAL VIEW [MyViewName]
This removes the view from the JSON configuration file. It can no longer be queried.
Schema for User Defined Views¶
User Defined Views are exposed in the UserViews
schema by default. This is done to avoid the view's name clashing with an actual entity in the data model. You can change the name of the schema used for UserViews by setting the UserViewsSchemaName
property.
Work with User Defined Views¶
For example, a SQL statement with a User Defined View called UserViews.RCustomers
only lists customers in Raleigh:
SELECT * FROM Customers WHERE City = 'Raleigh';
An example of a query to the driver:
SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';
Resulting in the effective query to the source:
SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';
That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.
SSL Configuration¶
Customize the SSL Configuration¶
By default, the connector attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.
To specify another certificate, see the SSLServerCert
property for the available formats to do so.
Data Model¶
The PayPal connector models two PayPal APIs as relational databases, the REST API and the SOAP API. Set the Schema
property and use the information in each section to select the proper API based on your project needs.
Using the REST API
The REST API is the newer API and is becoming more popular with PayPal users. However, the REST API lists only the transactions and actions made via the REST API and excludes the UI and the SOAP API.
See REST Data Model for the available entities in the REST API.
Using the SOAP API
The SOAP (Classic) API has full information on transactions, which includes transactions created from the UI, the REST API, and the SOAP API.
See SOAP Data Model for the available entities in the SOAP API.
REST Data Model¶
The PayPal connector models the PayPal REST APIs as database views and stored procedures. These are defined in schema files, which are simple, text-based configuration files that make schemas easy to customize.
This section provides the API limitations and requirements for the REST data model; you can use the SupportEnhancedSQL
feature, set by default, to circumvent most of these limitations.
Views¶
Views are tables that cannot be modified. Typically, read-only data are shown as views.
Stored Procedures¶
Stored Procedures are function-like interfaces to PayPal. They can be used to access other functionality in PayPal not surfaced in the Views.
PayPal REST API Limitations¶
There are some limitations in the PayPal REST API. For example, it provides no way to list all orders/payments, so to query the related views like OrderItems, OrderDetails, Payments you must know the respective ID and specify it.
Views¶
Views are similar to tables in the way that data is represented; however, views are read-only.
Queries can be executed against a view as if it were a normal table.
PayPal Connector Views¶
Name | Description |
---|---|
AuthorizationDetails | Query details about Authorizations. |
Balances | List all non crypto balances. |
CaptureDetails | Query details about a captured Payment. |
CreditCardDetails | Query details about the funding instrument of the payer, such as a Credit Card or a token that represents a Credit Card. |
CryptoBalances | List all crypto balances. |
DisputeDetails | PayPal merchants, partners, and external developers can use the PayPal Disputes API to manage disputes. |
Disputes | PayPal merchants, partners, and external developers can use the PayPal Disputes API to manage disputes. |
DisputeTransactions | PayPal merchants, partners, and external developers can use the PayPal Disputes API to manage disputes. |
Invoices | Query Invoices in PayPal. |
Notifications | Query the list of event types that are subscribed to a Webhook. |
OrderDetails | Query details about an Order. |
OrderItems | Get the items in an Order. |
Payments | Query details about authorized Payments. |
PaymentTransactions | Query Transaction details including the amount and item details. |
PayoutDetails | Retrieve details about Payouts. |
PayoutItems | Retrieve payout items from a Payout. |
Plans | List the Billing Plans. |
RefundDetails | Query details about a specific Refund. |
SaleDetails | Query details about a Sale Transaction. |
Subscriptions | Get billing and subscription details. |
SubscriptionTransactions | Lists transactions for a subscription. |
TransactionReports | Lists transaction reports. |
TransactionReportsCartInfoItemDetails | List Cart Info of Transaction Reports. |
TransactionReportsIncentiveDetails | List Incentive Details of Transaction Reports. |
Webhooks | Query the list of Webhooks. |
AuthorizationDetails¶
Query details about Authorizations.
Table Specific Information¶
Select
The only filter supported by the PayPal API is Id, which is required. The connector processes other filters client side within the connector.
For example, the following queries are processed server side and retrieve details for a specific Authorization.
SELECT * FROM AuthorizationDetails WHERE Id = '70K15739YA1955155'
SELECT * FROM AuthorizationDetails WHERE ID IN ('70K15739YA1955155', '70K15739YA1955156')
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The PayPal-generated ID for the authorized payment. | |
Status | String | The status for the authorized payment. | |
StatusDetails | String | The details of the authorized order pending status. | |
AmountTotal | Decimal | The amount for this authorized payment. | |
AmountCurrency | String | The currency of the amount. | |
InvoiceId | String | The API caller-provided external invoice number for this order. Appears in both the payer's transaction history and the emails that the payer receives. | |
CustomId | String | The API caller-provided external ID. Used to reconcile API caller-initiated transactions with PayPal transactions. Appears in transaction and settlement reports. | |
SellerProtectionStatus | String | The level of protection offered as defined by PayPal Seller Protection for Merchants. | |
ExpirationTime | Datetime | The amount being charged for the handling fee. Only supported when the payment method is set to paypal. | |
CreateTime | Datetime | The time of authorization. | |
UpdateTime | Datetime | The time that the resource was last updated. |
Balances¶
List all non crypto balances.
Table Specific Information¶
Select
This view returns a list of disputes for the authenticated user.
The following filters are supported by the PayPal API:
- Currency
- AsOfTime
The filters only support the = operator.
The connector processes other filters client side within the connector.
For example, the following queries are processed server side.
-
The following query retrieves details for a specific currency:
SELECT * FROM Balances WHERE Currency = 'USD'
-
The following query returns details for Balance records created before the specified date time:
SELECT * FROM Balances WHERE AsOfTime = '2022-01-09 20:59:59.0'
Columns¶
Name | Type | References | Description |
---|---|---|---|
Currency [KEY] | String | Currency Code of the balances listed. | |
AccountId | String | The PayPal payer ID, which is a masked version of the PayPal account number intended for use with third parties. | |
AvailableBalanceCurrencyCode | String | The three-character ISO-4217 currency code that identifies the currency. | |
AvailableBalanceValue | String | The value, which might be an integer or a decimal. | |
Primary | Boolean | Optional field representing if the currency is primary currency or not. | |
TotalBalanceCurrencyCode | String | The three-character ISO-4217 currency code that identifies the currency. | |
TotalBalanceValue | Decimal | The value, which might be:An integer or A decimal. | |
WithheldBalanceCurrencyCode | String | The three-character ISO-4217 currency code that identifies the currency. | |
WithheldBalanceValue | String | The value, which might be:An integer or A decimal. | |
AsOfTime | Datetime | The requested date and time or the last date and time when the balances can be served. | |
LastRefreshTime | Datetime | The date and time when the balances was last refreshed. |
CaptureDetails¶
Query details about a captured Payment.
Table Specific Information¶
Select
The only filter supported by the PayPal API is Id, which is required. The connector processes other filters client side within the connector.
For example, the following queries are processed server side and retrieve details for a specific Capture.
SELECT * FROM CaptureDetails WHERE Id = '9G02572041588110H'
SELECT * FROM CaptureDetails WHERE ID IN ('9G02572041588110H', '9G02572041588111H')
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The PayPal-generated ID for the captured payment. | |
Status | String | The status of the captured payment. | |
StatusDetails | String | The details of the captured payment status. | |
AmountValue | Decimal | The amount for this captured payment. | |
AmountCurrency | String | The currency of the amount for this captured payment. | |
InvoiceId | String | The API caller-provided external invoice number for this order. Appears in both the payer's transaction history and the emails that the payer receives. | |
CustomId | String | The API caller-provided external ID. Used to reconcile API caller-initiated transactions with PayPal transactions. Appears in transaction and settlement reports. | |
SellerProtectionStatus | String | The level of protection offered as defined by PayPal Seller Protection for Merchants. | |
FinalCapture | Boolean | Indicates whether you can make additional captures against the authorized payment. | |
DisbursementMode | String | The funds that are held on behalf of the merchant. | |
SellerGrossAmount | Decimal | The amount for this captured payment. | |
SellerPaypalFee | Decimal | The applicable fee for this captured payment. | |
SellerNetAmount | Decimal | The net amount that the payee receives for this captured payment in their PayPal account. | |
SellerReceivableAmount | Decimal | The net amount that is credited to the payee's PayPal account. Returned only when the currency of the captured payment is different from the currency of the PayPal account where the payee wants to credit the funds. | |
SellerExchangeRate | Decimal | The exchange rate that determines the amount that is credited to the payee's PayPal account. Returned when the currency of the captured payment is different from the currency of the PayPal account where the payee wants to credit the funds. | |
CreateTime | Datetime | The time of authorization. | |
UpdateTime | Datetime | The time that the resource was last updated. |
CreditCardDetails¶
Query details about the funding instrument of the payer, such as a Credit Card or a token that represents a Credit Card.
Table Specific Information¶
Select
This view returns a list of Credit Cards or details for a specific one.
The following filters are supported by the PayPal API:
- Id
- CreateTime
- MerchantId
- ExternalCardId
- ExternalCustomerId
The CreateTime filter supports the >, >=, <, <=, and = operators, while the other filters only support the = operator.
The connector processes other filters client side within the connector.
For example, the following queries are processed server side.
-
The following query retrieves details for a specific Credit Card:
SELECT * FROM CreditCardDetails WHERE ID = 'CARD-19H02474E2775740PKZYWVVI'
-
The following query returns details for Credit Card records created after the specified date:
SELECT * FROM CreditCardDetails WHERE CreateTime > '2016-01-01'
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The ID of the credit card. | |
Number | String | The redacted credit card number. | |
Type | String | The credit card type. Valid types are: visa, mastercard, discover, amex. | |
ExpireMonth | Integer | The expiration month with no leading zero. Acceptable values are 1 through 12. | |
ExpireYear | Integer | The 4-digit expiration year. | |
FirstName | String | The first name of the cardholder. | |
LastName | String | The last name of the cardholder. | |
AddressLine1 | String | The billing address associated with card. Line 1 of the address (e.g., number, street, etc.). | |
AddressLine2 | String | The optional line 2 of the Address (e.g., suite, apt number, etc.). | |
City | String | The city name. | |
CountryCode | String | The 2-letter country code. | |
PostalCode | String | The ZIP code or equivalent is usually required for countries that have them. | |
Phone | String | The phone number in E.123 format. | |
ExternalCustomerId | String | The unique identifier of the customer to whom this bank account belongs. Generated and provided by the facilitator. | |
MerchantId | String | The user-provided, optional field that functions as a unique identifier for the merchant holding the card. Note that this has no relation to PayPal merchant Id. | |
ExternalCardId | String | The unique identifier of the bank account resource. Generated and provided by the facilitator so it can be used to restrict the usage of the bank account to the specific merchant. | |
CreateTime | Datetime | The resource creation time in ISO8601 date-time format (e.g., 1994-11-05T13:15:30Z). | |
UpdateTime | Datetime | The resource update time in ISO8601 date-time format (e.g., 1994-11-05T13:15:30Z). | |
State | String | The state of the credit card funding instrument: expired or ok. | |
ValidUntil | Datetime | The funding instrument expiration date. |
CryptoBalances¶
List all crypto balances.
Table Specific Information¶
Select
This view returns a list of disputes for the authenticated user.
The following filters are supported by the PayPal API:
- AsOfTime
The filters only support the = operator.
The connector processes other filters client side within the connector.
For example, the following queries are processed server side.
-
The following query returns details for Balance records created before the specified date time:
SELECT * FROM CryptoBalances WHERE AsOfTime = '2022-01-09 20:59:59.0'
Columns¶
Name | Type | References | Description |
---|---|---|---|
AssetSymbol [KEY] | String | The Cryptocurrency ticker symbol / code as assigned by liquidity providers. | |
AccountId | String | The PayPal payer ID, which is a masked version of the PayPal account number intended for use with third parties. | |
Quantity | Decimal | Quantity of a cryptocurrency asset. | |
AsOfTime | Datetime | The requested date and time or the last date and time when the balances can be served. | |
LastRefreshTime | Datetime | The date and time when the balances were last refreshed. |
DisputeDetails¶
PayPal merchants, partners, and external developers can use the PayPal Disputes API to manage disputes.
Table Specific Information¶
Select
This view returns a list of disputes with detailed information for the authenticated user.
The following filters are supported by the PayPal API:
- DisputeId
The connector processes other filters client side within the connector.
For example, the following queries are processed server side.
-
The following query retrieves details for a specific Dispute:
SELECT * FROM DisputeDetails WHERE DisputeId = 'PP-D-12345'
Columns¶
Name | Type | References | Description |
---|---|---|---|
DisputeId [KEY] | String | The ID of the dispute. | |
CreatedTime | Datetime | The date and time when the dispute was created. | |
UpdatedTime | Datetime | The date and time when the dispute was updated. | |
Status | String | The status of the dispute. The allowed values are OPEN, WAITING_FOR_BUYER_RESPONSE, WAITING_FOR_SELLER_RESPONSE, UNDER_REVIEW, RESOLVED, OTHER. | |
Reason | String | The reason for the item-level dispute. The allowed values are MERCHANDISE_OR_SERVICE_NOT_RECEIVED, MERCHANDISE_OR_SERVICE_NOT_AS_DESCRIBED, UNAUTHORISED, CREDIT_NOT_PROCESSED, DUPLICATE_TRANSACTION, INCORRECT_AMOUNT, PAYMENT_BY_OTHER_MEANS, CANCELED_RECURRING_BILLING, PROBLEM_WITH_REMITTANCE, OTHER. | |
DisputeState | String | The state of the dispute. The allowed values are OPEN_INQUIRIES, REQUIRED_ACTION, REQUIRED_OTHER_PARTY_ACTION, UNDER_PAYPAL_REVIEW, APPEALABLE, RESOLVED. | |
DisputeCurrencyCode | String | The three-character ISO-4217 currency code that identifies the currency. | |
DisputeAmountValue | Decimal | The value of the amount. | |
ExternalReasonCode | String | The code that identifies the reason for the credit card chargeback. | |
DisputeOutcomeCode | String | The outcome of a resolved dispute. | |
DisputeOutcomeAmountRefundedCode | String | The three-character ISO-4217 currency code that identifies the currency. | |
DisputeOutcomeAmountRefundedValue | Decimal | The value of the amount. | |
DisputeLifecycleStage | String | The stage in the dispute lifecycle. | |
DisputeChannel | String | The channel where the customer created the dispute. | |
OfferType | String | The merchant-proposed offer type for the dispute. | |
BuyerRequestedAmountCode | String | The three-character ISO-4217 currency code that identifies the currency. | |
BuyerRequestedAmountValue | Decimal | The value of the amount. | |
BuyerResponseDueDate | Datetime | The date and time by when the customer must respond to the dispute. | |
SellerOfferedAmountCode | String | The three-character ISO-4217 currency code that identifies the currency. | |
SellerOfferedAmountValue | Decimal | The value of the amount. | |
SellerResponseDueDate | Datetime | The date and time by when the seller must respond to the dispute. | |
Email | String | The email address that is provided by the merchant where the customer can share the evidences. | |
Note | String | The merchant provided notes that are visible to both the customer and PayPal. | |
TimePosted | Datetime | The date and time when the contact details were posted. |
Disputes¶
PayPal merchants, partners, and external developers can use the PayPal Disputes API to manage disputes.
Table Specific Information¶
Select
This view returns a list of disputes for the authenticated user.
The following filters are supported by the PayPal API:
- DisputeId
- DisputeState
- CreateTime
The CreateTime filter supports the >, >=, <, <=, and = operators, while the other filters only support the = operator.
The connector processes other filters client side within the connector.
For example, the following queries are processed server side.
-
The following query retrieves details for a specific dispute:
SELECT * FROM Disputes WHERE DisputeId = 'PP-D-12345'
-
The following query returns details for Dispute records created after the specified date:
SELECT * FROM Disputes WHERE CreateTime > '2016-01-01'
Columns¶
Name | Type | References | Description |
---|---|---|---|
DisputeId [KEY] | String | The ID of the dispute. | |
CreatedTime | Datetime | The date and time when the dispute was created. | |
UpdatedTime | Datetime | The date and time when the dispute was updated. | |
Status | String | The status of the dispute. The allowed values are OPEN, WAITING_FOR_BUYER_RESPONSE, WAITING_FOR_SELLER_RESPONSE, UNDER_REVIEW, RESOLVED, OTHER. | |
Reason | String | The reason for the item-level dispute. The allowed values are MERCHANDISE_OR_SERVICE_NOT_RECEIVED, MERCHANDISE_OR_SERVICE_NOT_AS_DESCRIBED, UNAUTHORISED, CREDIT_NOT_PROCESSED, DUPLICATE_TRANSACTION, INCORRECT_AMOUNT, PAYMENT_BY_OTHER_MEANS, CANCELED_RECURRING_BILLING, PROBLEM_WITH_REMITTANCE, OTHER. | |
DisputeState | String | The state of the dispute. The allowed values are OPEN_INQUIRIES, REQUIRED_ACTION, REQUIRED_OTHER_PARTY_ACTION, UNDER_PAYPAL_REVIEW, APPEALABLE, RESOLVED. | |
DisputeCurrencyCode | String | The three-character ISO-4217 currency code that identifies the currency. | |
DisputeAmountValue | Decimal | The value of the amount. |
DisputeTransactions¶
PayPal merchants, partners, and external developers can use the PayPal Disputes API to manage disputes.
Table Specific Information¶
Select
This view returns a list of transactions with details for a specific dispute.
The following filters are supported by the PayPal API:
- DisputeId
The connector processes other filters client side within the connector.
For example, the following queries are processed server side.
-
The following query retrieves transaction details for a specific dispute:
SELECT * FROM DisputeTransactions WHERE DisputeId = 'PP-D-12345'
Columns¶
Name | Type | References | Description |
---|---|---|---|
DisputeId [KEY] | String | The ID of the dispute. | |
CreatedTime | Datetime | The date and time when the transaction was created. | |
TransactionStatus | String | The transaction status. | |
GrossAmountCode | String | The three-character ISO-4217 currency code that identifies the currency. | |
GrossAmountValue | Decimal | The value of the amount. | |
BuyerName | String | The customer's name. | |
BuyerTransactionId | String | The customer's transaction ID. | |
SellerEmail | String | The email address for the merchant's PayPal account. | |
SellerMerchantId | String | The PayPal account ID for the merchant. | |
SellerTransactionId | String | The ID, as seen by the merchant, for this transaction. | |
SellerName | String | The name of the merchant. | |
SellerProtectionEligible | Boolean | Indicator if the seller is eligible for protection. | |
Custom | String | A free-text field that is entered by the merchant during checkout. |
Invoices¶
Query Invoices in PayPal.
Table Specific Information¶
Select
The only filter supported by the PayPal API is Id. Server-side processing is only supported for the '=' operator.
SELECT * FROM Invoices WHERE ID = 'INV2-LV8Z-5XH2-L4Z6-7LQ4'
The connector processes other filters client side within the connector.
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The ID of the invoice. | |
ParentId | String | The parent ID to an invoice that defines the group invoice to which the invoice is related. | |
Status | String | The status of the invoice. | |
Reference | String | The reference data. Includes a post office (PO) number. | |
Currency | String | The three-character ISO-4217 currency code that identifies the currency. | |
Note | String | A note to the invoice recipient. Also appears on the invoice notification email. | |
TermsAndConditions | String | The general terms of the invoice. Can include return or cancellation policy and other terms and conditions. | |
Memo | String | A private bookkeeping memo for the user. | |
InvoiceDate | Datetime | The invoice date as specificed by the sender, in Internet date and time format. | |
InvoiceNumber | String | A private bookkeeping memo for the user. | |
TermType | String | The payment term. Payment can be due upon receipt, a specified date, or in a set number of days. | |
DueDate | Datetime | The date when the invoice payment is due, in Internet date and time format. | |
DueAmount | Decimal | The due amount, which is the balance amount outstanding after payments. | |
Gratuity | Decimal | The amount paid by the payer as gratuity to the invoicer. | |
PaidAmount | Decimal | The aggregated payment amounts against this invoice. | |
RefundAmount | Decimal | The aggregated refund amounts. | |
CreatedTime | Datetime | The date and time when the resource was created, in Internet date and time format. | |
CreatedBy | String | The email address of the account that created the resource. | |
LastUpdatedTime | Datetime | The date and time when the resource was last edited, in Internet date and time format. | |
LastUpdatedBy | String | The email address of the account that last edited the resource. | |
CancelTime | Datetime | The date and time when the resource was canceled, in Internet date and time format. | |
CancelledBy | String | The actor who canceled the resource. | |
FirstSentTime | Datetime | The date and time when the resource was first sent, in Internet date and time format. | |
LastSentTime | Datetime | The date and time when the resource was last sent, in Internet date and time format. | |
LastSentBy | String | The email address of the account that last sent the resource. | |
CreatedByFlow | String | The flow variation that created this invoice. | |
RecipientViewUrl | String | The URL for the invoice payer view hosted on paypal.com. | |
InvoicerViewUrl | String | The URL for the invoice merchant view hosted on paypal.com. | |
InvoicerBusinessName | String | The business name of the party. | |
InvoicerPrefixName | String | The prefix, or title, to the party's name. | |
InvoicerFirstName | String | When the party is a person, the party's given, or first, name. | |
InvoicerMiddleName | String | When the party is a person, the party's middle name. Use also to store multiple middle names including the patronymic, or father's, middle name. | |
InvoicerLastName | String | When the party is a person, the party's surname or family name. Also known as the last name. Required when the party is a person. | |
InvoicerSuffixName | String | The suffix for the party's name. | |
InvoicerFullName | String | When the party is a person, the party's full name. | |
InvoicerAddressLine1 | String | The first line of the address. For example, number or street. | |
InvoicerAddressLine2 | String | The second line of the address. For example, suite or apartment number. | |
InvoicerAddressLine3 | String | The third line of the address, if needed. | |
InvoicerAddressPostalCode | String | The postal code, which is the zip code or equivalent. Typically required for countries with a postal code or an equivalent. | |
InvoicerAddressCountryCode | String | The two-character ISO 3166-1 code that identifies the country or region. | |
InvoicerAddressStreetNumber | String | The street number. | |
InvoicerAddressStreetName | String | The street name. Just Drury in Drury Lane. | |
InvoicerAddressStreetType | String | The street type. For example, avenue, boulevard, road, or expressway. | |
InvoicerAddressDeliveryService | String | The delivery service. Post office box, bag number, or post office name. | |
InvoicerAddressBuildingName | String | A named locations that represents the premise. Usually a building name or number or collection of buildings with a common name or number. | |
InvoicerAddressSubBuilding | String | The first-order entity below a named building or location that represents the sub-premise. Usually a single building within a collection of buildings with a common name. | |
InvoicerEmail | String | The invoicer email address, which must be listed in the user's PayPal profile. | |
InvoicerWebsite | String | The invoicer's website. | |
InvoicerTaxId | String | The invoicer's tax ID. | |
InvoicerAdditionalNotes | String | Any additional information. Includes business hours. | |
InvoicerLogoUrl | String | The full URL to an external logo image. The logo image must not be larger than 250 pixels wide by 90 pixels high. | |
TaxCalculatedAfterDiscount | Boolean | Indicates whether the tax is calculated before or after a discount. | |
TaxInclusive | Boolean | Indicates whether the unit price includes tax. | |
AllowTip | Boolean | Indicates whether the invoice enables the customer to enter a tip amount during payment. | |
TemplateId | String | The template ID. The template determines the layout of the invoice. Includes which fields to show and hide. | |
AllowPartialPayment | Boolean | Indicates whether the invoice allows a partial payment. If false, the invoice must be paid in full. If true, the invoice allows partial payments. | |
MinimumAmountDue | String | The minimum amount allowed for a partial payment. Valid only when allow_partial_payment is true. | |
InvoiceAmount | Decimal | The invoice amount summary of item total, discount, tax total and shipping. | |
InvoiceCurrency | String | The invoice amount summary of item total, discount, tax total and shipping. | |
ItemTotal | Decimal | The subtotal for all items. Must equal the sum of (quantity * price) for all items. | |
InvoiceDiscountPercentage | String | The discount as a percentage value. Value is from 0 to 100. Supports up to five decimal places. | |
InvoiceDiscountValue | Decimal | The invoice level discount amount. Value is from 0 to 1000000. Supports up to two decimal places. | |
ItemDiscountValue | Decimal | The discount as an amount at item level. | |
TaxTotal | Decimal | The aggregated amount of the item and shipping taxes. | |
ShippingAmount | Decimal | The shipping fee for all items. Includes tax on shipping. | |
ShippingTaxAmount | Decimal | The calculated tax amount. The tax amount is added to the item total. | |
ShippingTaxPercentage | String | The tax rate. Value is from 0 to 100. Supports up to five decimal places. | |
ShippingTaxName | String | The name of the tax applied on the invoice items. |
Notifications¶
Query the list of event types that are subscribed to a Webhook.
Table Specific Information¶
Select
The only filter supported by the PayPal API is WebhookId, which only supports the = operator. This is a required parameter.The connector processes other filters client side within the connector.
For example, the following query is processed server side and retrieves the Notifications list for the specified Webhook.
SELECT * FROM Notifications WHERE WebhookId = '8BU398853V564980H'
Columns¶
Name | Type | References | Description |
---|---|---|---|
Name | String | The unique event type name. | |
Description | String | The human-readable description of the event type. | |
WebhookId | String | Webhook Id. |
OrderDetails¶
Query details about an Order.
Table Specific Information¶
Select
Note
PayPal REST API does not give us a way to list all OrderId-s, so you must specify it. If you need such use case, it's recommended to use the Transactions and TransactionDetails views in SOAP schema.
The only filter supported by the PayPal API is Id, which is required. The connector processes other filters client side within the connector.
For example, the following queries are processed server side and retrieve details for a specific Order.
SELECT * FROM OrderDetails WHERE Id = 'O-1HE90236LH9332431'
SELECT * FROM OrderDetails WHERE ID IN ('O-1HE90236LH9332431', 'O-7D635956955612146')
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The ID of the order. | |
Status | String | The order status. The possible values are: CREATED, SAVED, APPROVED, VOIDED, COMPLETED. | |
Intent | String | The intent to either capture payment immediately or authorize a payment for an order after order creation. The possible values are: CAPTURE, AUTHORIZE. | |
CreatedTime | Datetime | The date and time when the transaction occurred. | |
UpdatedTime | Datetime | The date and time when the transaction was last updated. | |
PayerId | String | The ID of the customer who approves and pays for the order. The customer is also known as the payer. | |
PayerGivenName | String | The name of the customer who approves and pays for the order. The customer is also known as the payer. | |
PayerSurname | String | The last name of the customer who approves and pays for the order. The customer is also known as the payer. | |
PayerEmail | String | The email of the customer who approves and pays for the order. The customer is also known as the payer. | |
PayerPhoneType | String | The phone type. | |
PayerPhoneNumber | String | The phone number, in its canonical international E.164 numbering plan format. | |
PayerBirthDate | String | The birth date of the payer in YYYY-MM-DD format. | |
PayerTaxId | String | The customer's tax ID. Supported for the PayPal payment method only. | |
PayerTaxType | String | The customer's tax ID type. Supported for the PayPal payment method only. | |
PayerAddressLine1 | String | The first line of the address. For example, number or street. | |
PayerAddressLine2 | String | The second line of the address. For example, suite or apartment number. | |
PayerAddressPostalCode | String | The postal code, which is the zip code or equivalent. Typically required for countries with a postal code or an equivalent. | |
PayerAddressCountryCode | String | The two-character ISO 3166-1 code that identifies the country or region. |
OrderItems¶
Get the items in an Order.
Table Specific Information¶
Select
Note
PayPal REST API does not give us a way to list all OrderId-s, so you must specify it. If you need such use case, it's recommended to use the Transactions and TransactionItems views in SOAP schema.
The only filter supported by the PayPal API is OrderId, which is required. The connector processes other filters client side within the connector.
For example, the following queries are processed server side and retrieve details for a specific Order.
SELECT * FROM OrderItems WHERE OrderId = 'O-1HE90236LH9332431'
SELECT * FROM OrderItems WHERE OrderId IN ('O-1HE90236LH9332431', 'O-7D635956955612146')
Columns¶
Name | Type | References | Description |
---|---|---|---|
OrderId | String | The ID of the Order this Item belongs to. | |
Id | String | The PayPal-generated ID for the purchase unit. This ID appears in both the payer's transaction history and the emails that the payer receives. | |
ReferenceId | String | The API caller-provided external ID for the purchase unit. Required for multiple purchase units when you must update the order through PATCH. | |
Amount | String | The total order amount with an optional breakdown that provides details, such as the total item amount, total tax amount, shipping, handling, insurance, and discounts, if any. | |
Description | String | The purchase description. | |
CustomId | String | The API caller-provided external ID. Used to reconcile API caller-initiated transactions with PayPal transactions. Appears in transaction and settlement reports. | |
InvoiceId | String | The API caller-provided external invoice ID for this order. | |
SoftDescriptor | String | The payment descriptor on account transactions on the customer's credit card statement. | |
ShipperFullName | String | When the party is a person, the party's full name. | |
ShippingAddressLine1 | String | The first line of the address. For example, number or street. | |
ShippingAddressLine2 | String | The second line of the address. For example, suite or apartment number. | |
ShippingAddressPostalCode | String | The postal code, which is the zip code or equivalent. Typically required for countries with a postal code or an equivalent. | |
ShippingAddressCountryCode | String | The two-character ISO 3166-1 code that identifies the country or region. | |
PayeeEmail | String | The email address of merchant. | |
PayeeMerchantId | String | The encrypted PayPal account ID of the merchant. | |
DisbursementMode | String | The funds that are held on behalf of the merchant. |
Payments¶
Query details about authorized Payments.
Table Specific Information¶
Select
The only supported filters are ID and OrderId. Filters only supports the = operator The connector processes other filters client side within the connector.
For example, the following query is processed server side and retrieves Payments that were created after the specified date.
SELECT * FROM Payments WHERE ID > 'P-41250-2' AND OrderId = 'O-123550-05'
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The PayPal-generated ID for the authorized payment. | |
Intent | String | The payment intent. | |
PayerPaymentMethod | String | The payment method. | |
PayerStatus | String | The status of payer's PayPal account. | |
PayerEmail | String | The payer's email address. | |
PayerSalutation | String | The payer's salutation. | |
PayerFirstName | String | The payer's first name. | |
PayerMiddleName | String | The payer's middle name. | |
PayerLastName | String | The payer's last name. | |
PayerSuffix | String | The payer's suffix. | |
PayerId | String | The PayPal-assigned encrypted payer ID. | |
PayerBirthDate | Date | The birth date of the payer, in Internet date format. | |
PayerTaxId | String | The payer's tax ID. Supported for the PayPal payment method only. | |
PayerTaxType | String | The payer's tax ID type. Supported for the PayPal payment method only. | |
PayerBillingAddressLine1 | String | The first line of the address. For example, number, street, and so on. | |
PayerBillingAddressLine2 | String | The second line of the address. For example, suite or apartment number. | |
PayerBillingAddressCity | String | The city name. | |
PayerBillingAddressCountryCode | String | The two-character ISO 3166-1 code that identifies the country or region. | |
PayerBillingAddressPostalCode | String | The postal code, which is the zip code or equivalent. | |
PayerBillingAddressState | String | The code for a US state or the equivalent for other countries. | |
PayerBillingAddressPhone | String | The phone number, in E.123 format. | |
PayerBillingAddressNormalizationStatus | String | The address normalization status. Returned only for payers from Brazil. | |
PayerBillingAddressType | String | The type of address. For example, HOME_OR_WORK, GIFT, and so on. | |
PayerShippingAddressLine1 | String | The first line of the address. For example, number, street, and so on. | |
PayerShippingAddressLine2 | String | The second line of the address. For example, suite or apartment number. | |
PayerShippingAddressCity | String | The city name. | |
PayerShippingAddressCountryCode | String | The two-character ISO 3166-1 code that identifies the country or region. | |
PayerShippingAddressPostalCode | String | The postal code, which is the zip code or equivalent. | |
PayerShippingAddressState | String | The code for a US state or the equivalent for other countries. | |
PayerShippingAddressPhone | String | The phone number, in E.123 format. | |
PayerShippingAddressNormalizationStatus | String | The address normalization status. Returned only for payers from Brazil. | |
PayerShippingAddressType | String | The type of address. For example, HOME_OR_WORK, GIFT, and so on. | |
State | String | The state of the payment, authorization, or order transaction. | |
ExperienceProfileId | String | The PayPal-generated ID for the merchant's payment experience profile. For information, see create web experience profile. | |
NoteToPayer | String | A free-form field that clients can use to send a note to the payer. | |
FailureReason | String | The reason code for a payment failure. | |
ReturnUrl | String | The URL where the payer is redirected after he or she approves the payment. Required for PayPal account payments. | |
CancelUrl | String | The URL where the payer is redirected after he or she cancels the payment. Required for PayPal account payments. | |
BrandName | String | A label that overrides the business name in the merchant's PayPal account on the PayPal checkout pages. | |
Locale | String | The locale of pages that the PayPal payment experience displays. | |
LandingPage | String | The type of landing page to show on the PayPal site for customer checkout. | |
ShippingPreference | String | The shipping preference. | |
UserAction | String | The user action. Presents the customer with either the Continue or Pay Now checkout flow. | |
CreatedTime | Datetime | The date and time when the transaction occurred. | |
UpdatedTime | Datetime | The date and time when the payment was updated, in Internet date and time format. |
PaymentTransactions¶
Query Transaction details including the amount and item details.
Table Specific Information¶
Select
The only filter supported by the PayPal API is PaymentId, which is required. The connector processes other filters client side within the connector.
For example, the following queries are processed server side and retrieve a list of Transactions for the specified Payment.
SELECT * FROM PaymentTransactions WHERE PaymentId = 'PAY-6SL93120VR4146201KZYWPVY'
SELECT * FROM PaymentTransactions WHERE PaymentId IN ('PAY-6SL93120VR4146201KZYWPVY', 'PAY-6SL93120VR0000201KZYWPVY')
Columns¶
Name | Type | References | Description |
---|---|---|---|
PaymentId | String | Payments.Id | The ID of the payment. |
Amount | Decimal | The amount to collect. | |
Currency | String | The currency of the amount to collect. | |
PayeeEmail | String | The email address associated with the payee's PayPal account. | |
PayeeMerchantId | String | The PayPal account ID for the payee. | |
Description | String | The purchase description. | |
NoteToPayee | String | The note to the recipient of the funds in this transaction. | |
Custom | String | The free-form field for the client's use. | |
InvoiceNumber | String | The invoice number to track this payment. | |
SoftDescriptor | String | The soft descriptor to use to charge this funding source. If greater than the maximum allowed length, the API truncates the string. | |
NotifyUrl | String | The URL to send payment notifications. | |
AllowedPaymentMethod | String | The payment method for this transaction. This field does not apply to the credit card payment method. | |
RecipientName | String | The name of the recipient. | |
ShippingPhoneNumber | String | The shipping phone number, in its canonical international format as defined by the E.164 numbering plan. | |
ShippingAddressLine1 | String | The first line of the address. For example, number, street, and so on. | |
ShippingAddressLine2 | String | The second line of the address. For example, suite or apartment number. | |
ShippingAddressCity | String | The city name. | |
ShippingAddressCountryCode | String | The two-character ISO 3166-1 code that identifies the country or region. | |
ShippingAddressPostalCode | String | The postal code, which is the zip code or equivalent. | |
ShippingAddressState | String | The code for a US state or the equivalent for other countries. | |
ShippingAddressPhone | String | The phone number, in E.123 format. | |
ShippingAddressNormalizationStatus | String | The address normalization status. Returned only for payers from Brazil. | |
ShippingAddressType | String | The type of address. For example, HOME_OR_WORK, GIFT, and so on. |
PayoutDetails¶
Retrieve details about Payouts.
Table Specific Information¶
Select
The only filter supported by the PayPal API is PayoutBatchId, which is required. The connector processes other filters client side within the connector.
For example, the following query is processed server side and retrieves details for a specific Payout.
SELECT * FROM PayoutDetails WHERE PayoutBatchId = 'HSY55UPD25KF2'
Columns¶
Name | Type | References | Description |
---|---|---|---|
PayoutBatchId [KEY] | String | The ID of the payout for which to show details. | |
BatchStatus | String | The PayPal-generated payout status. If the payout passes preliminary checks, the status is PENDING. | |
TimeCreated | Datetime | The date and time when processing for the payout began | |
TimeCompleted | Datetime | The date and time when processing for the payout completed. | |
SenderBatchId | String | The sender-specified ID number. Tracks the payout in an accounting system. | |
SenderEmailSubject | String | The subject line for the email that PayPal sends when payment for a payout item completes. The subject line is the same for all recipients. | |
Amount | Decimal | The total amount, requested for the payouts. | |
Currency | String | The currency of the total amount | |
FeeAmount | Decimal | The amount of the total estimate for the applicable payouts fees. Initially, the fee is 0. The fee is populated after the payout moves to the PROCESSING state. | |
FeeCurrency | String | The currency for the applicable payouts fees. |
PayoutItems¶
Retrieve payout items from a Payout.
Table Specific Information¶
Select
The only filter supported by the PayPal API is PayoutBatchId, which is required. The connector processes other filters client side within the connector.
For example, the following query is processed server side and retrieves all payout items for a specific Payout.
SELECT * FROM PayoutItems WHERE PayoutBatchId = 'HSY55UPD25KF2'
Columns¶
Name | Type | References | Description |
---|---|---|---|
PayoutBatchId [KEY] | String | The ID of the payout for which to show payout items details. | |
PayoutItemId [KEY] | String | The ID for the payout item | |
TransactionId | String | The PayPal-generated ID for the transaction. | |
TransactionStatus | String | The transaction status. | |
FeeAmount | Decimal | The amount for the applicable payout fee. Initially, the fee is 0. The fee is populated after the item moves to the PENDING state | |
FeeCurrency | String | The currency for the applicable payout fee | |
RecipientType | String | The recipient type.Value is: EMAIL, PHONE, PAYPAL_ID. | |
AmountValue | Decimal | The amount of payout item. | |
AmountCurrency | String | The currency of payout item. | |
Note | String | A sender-specified note for notifications. | |
Receiver | String | The receiver of the payment. Corresponds to the recipient_type value in the request. | |
SenderItemId | String | A sender-specified ID number. Tracks the payout in an accounting system. | |
TimeProcessed | Datetime | The date and time when this item was last processed. | |
ErrorsMessage | String | The message that describes the error, if any. |
Plans¶
List the Billing Plans.
Table Specific Information¶
Select
This view returns a list of subscription plans for the authenticated user.
The following filters are supported by the PayPal API:
- Id
- ProductId
SELECT * FROM Plans WHERE Id = '1234'
SELECT * FROM Plans WHERE ProductId = '1234'
The connector processes other filters client side within the connector.
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The unique PayPal-generated ID for the plan. | |
CreateTime | Datetime | The date and time when the plan was created | |
Description | String | The detailed description of the plan. | |
BillingCyclesVersion | Integer | The active pricing scheme for this billing cycle. | |
BillingCyclesPricingModel | String | The active pricing scheme for this billing cycle. | |
BillingCyclesFrequency | String | The frequency details for this billing cycle. | |
BillingCyclesTenureType | String | The tenure type of the billing cycle. | |
BillingCyclesSequence | Integer | The order in which this cycle is to run among other billing cycles. | |
BillingCyclesTotalCycles | Integer | The number of times this billing cycle gets executed. | |
Links | String | contains the link of plan object | |
Name | String | The plan name. | |
ProductId | String | The ID for the product. | |
QuantitySupported | Boolean | Indicates whether you can subscribe to this plan by providing a quantity for the goods or service.. | |
Status | String | The plan status. | |
TaxesInclusive | Boolean | Indicates whether the tax was already included in the billing amount. | |
TaxesPercentage | String | The tax percentage on the billing amount. | |
UpdateTime | Datetime | The date and time when the plan was last updated. |
RefundDetails¶
Query details about a specific Refund.
Table Specific Information¶
Select
The only filter supported by the PayPal API is Id, which is required. The connector processes other filters client side within the connector.
For example, the following queries are processed server side and retrieve details for a specific Refund.
SELECT * FROM RefundDetails WHERE Id = '4W92068757818534U'
SELECT * FROM RefundDetails WHERE ID IN ('4W92068757818534U', '4W91234757818534U')
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The PayPal-generated ID for the captured payment. | |
Status | String | The status of the captured payment. | |
StatusDetails | String | The details of the captured payment status. | |
AmountValue | Decimal | The amount for this captured payment. | |
AmountCurrency | String | The currency of the amount for this captured payment. | |
InvoiceId | String | The API caller-provided external invoice number for this order. Appears in both the payer's transaction history and the emails that the payer receives. | |
NoteToPayer | String | The reason for the refund. Appears in both the payer's transaction history and the emails that the payer receives. | |
SellerGrossAmount | Decimal | The amount for this captured payment. | |
SellerPaypalFee | Decimal | The applicable fee for this captured payment. | |
SellerNetAmount | Decimal | The net amount that the payee receives for this captured payment in their PayPal account. | |
TotalRefundedMoney | Decimal | The total amount refunded from the original capture to date. | |
CreateTime | Datetime | The time of authorization. | |
UpdateTime | Datetime | The time that the resource was last updated. |
SaleDetails¶
Query details about a Sale Transaction.
Table Specific Information¶
Select
The only filter supported by the PayPal API is Id, which is required. The connector processes other filters client side within the connector.
For example, the following queries are processed server side and retrieve details for a specific Sale.
SELECT * FROM SaleDetails WHERE Id = '6M2430095X488331A'
SELECT * FROM SaleDetails WHERE ID IN ('6M2430095X488331A', '6M2410805X488331A')
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The ID of the sale transaction. | |
State | String | The state of the sale. One of the following: pending, completed, refunded, or partially_refunded. | |
Currency | String | The 3-letter currency code. | |
Subtotal | Decimal | The amount of the subtotal of the items. | |
TaxAmount | Decimal | The amount charged for tax. | |
ShippingAmount | Decimal | The amount charged for shipping. | |
ShippingDiscount | Decimal | The amount being discounted for the shipping fee. Only supported when the payment method is set to paypal. | |
HandlingFeeAmount | Decimal | The amount being charged for the handling fee. Only supported when the payment method is set to paypal. | |
InsuranceAmount | Decimal | The amount being charged for the insurance fee. Only supported when the payment method is set to paypal. | |
TotalAmount | Decimal | The total amount charged from the payer to the payee. In case of a refund, this is the refunded amount to the original payer from the payee. | |
ClearingTime | Datetime | The expected clearing time for e-check transactions. Only supported when the payment method is set to paypal. | |
CreateTime | Datetime | The time of sale. | |
Description | String | The description of the sale. | |
ExchangeRate | String | The exchange rate applied for this transaction. Returned only in cross-currency use cases where a merchant bills a buyer in a non-primary currency for that buyer. | |
FmfDescription | String | The description of the filter. | |
FmfFilterId | String | The name of the fraud management filter. One of the following: MAXIMUM_TRANSACTION_AMOUNT (basic filter), UNCONFIRMED_ADDRESS (basic filter), COUNTRY_MONITOR (basic filter), AVS_NO_MATCH (address verification service no match -- advanced filter), AVS_PARTIAL_MATCH (address verification service partial match -- advanced filter), AVS_UNAVAILABLE_OR_UNSUPPORTED (address verification service unavailable or not supported -- advanced filter), CARD_SECURITY_CODE_MISMATCH (advanced filter), BILLING_OR_SHIPPING_ADDRESS_MISMATCH (advanced filter), RISKY_ZIP_CODE (high risk lists filter), SUSPECTED_FREIGHT_FORWARDER_CHECK (high risk lists filter), RISKY_EMAIL_ADDRESS_DOMAIN_CHECK (high risk lists filter), RISKY_BANK_IDENTIFICATION_NUMBER_CHECK (high risk lists filter), RISKY_IP_ADDRESS_RANGE (high risk lists filter), LARGE_ORDER_NUMBER (transaction data filter), TOTAL_PURCHASE_PRICE_MINIMUM (transaction data filter), IP_ADDRESS_VELOCITY (transaction data filter), and PAYPAL_FRAUD_MODEL (transaction data filter). | |
FmfFilterType | String | The type of the fraud management filter. One of the following: ACCEPT (an ACCEPT filter is triggered only for the TOTAL_PURCHASE_PRICE_MINIMUM filter setting and is returned only in direct credit card payments where payment is accepted), PENDING (triggers a PENDING filter action where you need to explicitly accept or deny the transaction), DENY (triggers a DENY action where payment is denied automatically), and REPORT (triggers the Flag testing mode where payment is accepted). | |
FmfName | String | The name of the filter. | |
ParentPayment | String | The ID of the payment resource on which this transaction is based. | |
PaymentMode | String | The payment mode of the transaction. Only supported when the payment method is set to paypal. One of the following: INSTANT_TRANSFER, MANUAL_BANK_TRANSFER, DELAYED_TRANSFER, or ECHECK. | |
PendingReason | String | The reason the transaction is in a pending state. Only supported when the payment method is set to paypal. One of the following: PAYER-SHIPPING-UNCONFIRMED, MULTI-CURRENCY, RISK-REVIEW, REGULATORY-REVIEW, VERIFICATION-REQUIRED , ORDER, or OTHER. | |
ProtectionEligibility | String | The level of seller protection in force for the transaction. Only supported when the payment method is set to paypal. One of the following: ELIGIBLE, PARTIALLY_ELIGIBLE, INELIGIBLE. | |
ProtectionEligibilityType | String | The kind of seller protection in force for the transaction. This property is returned only when the ProtectionEligibility property is set to ELIGIBLE or PARTIALLY_ELIGIBLE. Only supported when the payment method is set to paypal. One of the following: ITEM_NOT_RECEIVED_ELIGIBLE or UNAUTHORIZED_PAYMENT_ELIGIBLE. One or both of the allowed values can be returned. | |
ReasonCode | String | The reason code for the transaction state being Pending or Reversed. Only supported when the payment method is set to paypal. One of the following: CHARGEBACK, GUARANTEE, BUYER_COMPLAINT, REFUND, UNCONFIRMED_SHIPPING_ADDRESS, ECHECK, INTERNATIONAL_WITHDRAWAL, RECEIVING_PREFERENCE_MANDATES_MANUAL_ACTION, PAYMENT_REVIEW, REGULATORY_REVIEW, UNILATERAL, or VERIFICATION_REQUIRED. | |
ReceiptId | String | The 16-digit number payment identification number returned for guest users to identify the payment. | |
ReceivableAmount | Decimal | The receivable amount. | |
TransactionFee | Decimal | The transaction fee. | |
UpdateTime | Datetime | The time that the sale was last updated. |
Subscriptions¶
Get billing and subscription details.
Table Specific Information¶
Select
The only filter supported by the PayPal API is Id, which is required. The connector processes other filters client side within the connector.
SELECT * FROM Subscriptions WHERE ID = '1234'
Note
We can get get the SubscriptionId by creating the subscription using CreateSubscription Stored procedure.
The connector processes other filters client side within the connector.
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The PayPal-generated ID for the subscription. | |
BillingInfocycle_executions | String | The trial and regular billing executions. | |
BillingInfo_FailedPaymentsCount | Integer | The Failed payments count. | |
BillingInfo_LastPaymentAmountCurrencyCode | String | The last payment amount currency code. | |
BillingInfo_LastPaymentAmountValue | String | The last payment amount. | |
BillingInfo_LastPaymentTime | Datetime | The date and time when the last payment was made. | |
BillingInfo_NextBillingTime | Datetime | The next date and time for billing this subscription. | |
BillingInfo_OutstandingBalanceCurrencyCode | String | The total pending bill amount, to be paid by the subscriber. | |
BillingInfo_outstandingBalanceValue | String | The total pending bill amount, to be paid by the subscriber. | |
CreateTime | Datetime | The date and time of subscription created. | |
Links | String | Links for the subscription objects. | |
PlanId | String | The ID of the plan. | |
Quantity | Integer | The quantity of the product in the subscription. | |
ShippingAmountCurrencyCode | String | The currency for a financial transaction, such as a balance or payment due. | |
ShippingAmountValue | String | The amount for a financial transaction, such as a balance or payment due. | |
StartTime | Datetime | The start date and time of the subscription. | |
Status | String | The status of the subscription. | |
StatusUpdateTime | Datetime | The date and time of status updated. | |
SubscriberEmailAddress | String | The email address of the payer. | |
SubscriberName_GivenName | String | The given name of the payer. | |
SubscriberName_Surname | String | The sur name of the payer. | |
SubscriberPayerId | String | The PayPal-assigned ID for the payer. | |
SubscriberShippingAddress_AddressAddressLine1 | String | Primary address of the payer. | |
SubscriberShippingAddress_AddressAddressLine2 | String | Secondary address of the payer. | |
SubscriberShippingAddress_AddressAdminArea1 | String | Primary address of the payer. | |
SubscriberShippingAddress_AddressAdminArea2 | String | Secondary address of the payer. | |
SubscriberShippingAddress_AddressCountryCode | String | Country code of the payer address. | |
SubscriberShippingAddress_AddressPostalCode | String | Postal code of the payer address. | |
SubscriberShippingAddress_NameFullName | String | Full name of the Payer. | |
UpdateTime | Datetime | The date and time of subscription updated. |
SubscriptionTransactions¶
Lists transactions for a subscription.
Table Specific Information¶
Select
This view returns a list of transactions with details for a specific subscription.
The following filters are supported by the PayPal API:
- SubscriptionId
- StartTime
- EndTime
SELECT * FROM SubscriptionTransactions WHERE SubscriptionId = '1234'
SELECT * FROM SubscriptionTransactions WHERE SubscriptionId = '1234' AND StartTime = '2018-01-21T07:50:20.940Z' AND EndTime = '2018-08-21T07:50:20.940Z'
Note
- SubscriptionId, StartTime and EndTime are the required parameters for this view and if StartTime and EndTime are not specified in the query the default values will be assigned from the code.
2. We can get get the SubscriptionId by creating the subscription using CreateSubscription Stored procedure.
The connector processes other filters client side within the connector.
Columns¶
Name | Type | References | Description |
---|---|---|---|
SubscriptionId | String | Subscriptions.Id | The ID of the subscription. |
AmountWithBreakdown_FeeAmountCurrencyCode | String | The fee details for the transaction. | |
AmountWithBreakdown_FeeAmountValue | String | The fee details for the transaction. | |
AmountWithBreakdown_GrossAmountCurrencyCode | String | The three-character ISO-4217 currency code that identifies the currency. | |
AmountWithBreakdown_GrossAmountValue | String | The value of the currency. | |
AmountWithBreakdown_NetAmountCurrencyCode | String | The currency code of the net amount that the payee receives for this transaction in their PayPal account. | |
AmountWithBreakdown_NetAmountValue | String | The net amount that the payee receives for this transaction in their PayPal account. | |
PayerEmail | String | The email ID of the customer. | |
PayerNameGivenName | String | When the party is a person, the party's given, or first, name. | |
PayerNameSurname | String | When the party is a person, the party's surname or family name. Also known as the last name. | |
Status | String | The initial state of the plan. | |
Time | Datetime | The date and time when the transaction was processed. |
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 |
---|---|---|
StartTime | Datetime | |
EndTime | Datetime |
TransactionReports¶
Lists transaction reports.
Table Specific Information¶
Select
The filters supported by the PayPal API are StartDate, EndDate, TransactionId, PaymentInstrumentType, BalanceAffectingRecordsOnly, StoreId, TerminalId, TransactionAmount, TransactionCurrency, TransactionStatus, TransactionType. Server-side processing is only supported for the '=' operator.
SELECT * FROM TransactionReports WHERE StartDate = '2021-09-06 12:12:12' AND EndDate = '2021-09-11 12:12:12'
The connector processes other filters client side within the connector.
Columns¶
Name | Type | References | Description |
---|---|---|---|
AccountNumber | String | The merchant account number. | |
LastRefreshedDatetime | Datetime | The date and time when the data was last refreshed, in Internet date and time format. | |
AuctionInfoAuctionBuyerId | String | The ID of the buyer who makes the purchase in the auction. | |
AuctionInfoAuctionClosingDate | Datetime | The date and time when the auction closes. | |
AuctionInfoAuctionItemSite | String | The auction site URL. | |
AuctionInfoAuctionSite | String | The name of the auction site. | |
PayerInfoAccountId | String | The PayPal customer account ID. | |
PayerInfoAddressStatus | String | The address status of the payer. | |
PayerInfoCountryCode | String | The two-character ISO 3166-1 code that identifies the country or region of the payer. | |
PayerInfoEmailAddress | String | The email address of the payer. | |
PayerInfoPayerNameAlternateFullName | String | The party's alternate name. | |
PayerInfoPayerNameGivenName | String | When the party is a person, the party's given, or first, name. | |
PayerInfoPayerNameSurname | String | When the party is a person, the party's surname or family name. Also known as the last name. Required when the party is a person. Use also to store multiple surnames including the matronymic, or mother's, surname. | |
PayerInfoPayerStatus | String | The status of the payer. Value is Y or N. | |
ShippingInfoAddressCity | String | The city name. | |
ShippingInfoAddressCountryCode | String | The two-character ISO 3166-1 code that identifies the country or region. | |
ShippingInfoAddressLine1 | String | The first line of the address. For example, number or street. | |
ShippingInfoAddressLine2 | String | The second line of the address. For example, suite or apartment number. | |
ShippingInfoAddressPostalCode | String | The postal code, which is the zip code or equivalent. | |
ShippingInfoName | String | The recipient's name. | |
StoreInfoStoreId | String | The ID of a store for a merchant in the system of record. | |
StoreInfoTerminalId | String | The terminal ID for the checkout stand in a merchant store. | |
TransactionInfoCustomField | String | The merchant-provided custom text. | |
TransactionInfoFeeAmountCurrencyCode | String | The three-character ISO-4217 currency code that identifies the currency. | |
TransactionInfoFeeAmountValue | String | The value of fee. | |
TransactionInfoInsuranceAmountCurrencyCode | String | The three-character ISO-4217 currency code that identifies the currency. | |
TransactionInfoInsuranceAmountValue | String | The value of insurance amount. | |
TransactionInfoInvoiceId | String | The invoice ID that is sent by the merchant with the transaction. | |
TransactionInfoPaypalAccountId | String | The ID of the PayPal account of the counterparty. | |
TransactionInfoProtectionEligibility | String | Indicates whether the transaction is eligible for protection. | |
TransactionInfoShippingAmountCurrencyCode | String | The three-character ISO-4217 currency code that identifies the currency. | |
TransactionInfoShippingAmountValue | String | The value of shipping amount. | |
TransactionInfoShippingDiscountAmountCurrency_code | String | The three-character ISO-4217 currency code that identifies the currency. | |
TransactionInfoShippingDiscountAmountValue | String | The value of shipping discount amount. | |
TransactionInfoTransactionAmountCurrencyCode | String | The three-character ISO-4217 currency code that identifies the currency. | |
TransactionInfoTransactionAmountValue | String | The value of transaction amount. | |
TransactionInfoTransactionEventCode | String | A five-digit transaction event code that classifies the transaction type based on money movement and debit or credit. | |
TransactionInfoTransactionId | String | The PayPal-generated transaction ID. | |
TransactionInfoTransactionInitiationDate | Datetime | The date and time when work on a transaction began in the PayPal system, as expressed in the time zone of the account on this side of the payment. | |
TransactionInfoTransactionNote | String | A special note that the payer passes to the payee. Might contain special customer requests, such as shipping instructions. | |
TransactionInfoTransactionStatus | String | A code that indicates the transaction status. | |
TransactionInfoTransactionSubject | String | The subject of payment. The payer passes this value to the payee. The payer controls this data through the interface through which he or she sends the data. | |
TransactionInfoTransactionUpdatedDate | Datetime | The date and time when the transaction was last changed, as expressed in the time zone of the account on this side of the payment. |
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 |
---|---|---|
StartDate | Datetime | |
EndDate | Datetime | |
TransactionId | String | |
PaymentInstrumentType | String | |
BalanceAffectingRecordsOnly | String | |
StoreId | String | |
TerminalId | String | |
TransactionAmount | String | |
TransactionCurrency | String | |
TransactionStatus | String | |
TransactionType | String |
TransactionReportsCartInfoItemDetails¶
List Cart Info of Transaction Reports.
Table Specific Information¶
Select
The filters supported by the PayPal API are StartDate, EndDate, TransactionId, PaymentInstrumentType, BalanceAffectingRecordsOnly, StoreId, TerminalId, TransactionAmount, TransactionCurrency, TransactionStatus, TransactionType. Server-side processing is only supported for the '=' operator.
SELECT * FROM TransactionReportsCartInfoItemDetails WHERE StartDate = '2021-09-06 12:12:12' AND EndDate = '2021-09-11 12:12:12'
The connector processes other filters client side within the connector.
Columns¶
Name | Type | References | Description |
---|---|---|---|
AccountNumber | String | The merchant account number. | |
LastRefreshedDatetime | Datetime | The date and time when the data was last refreshed, in Internet date and time format. | |
InvoiceNumber | String | The invoice number. An alphanumeric string that identifies a billing for a merchant. | |
ItemAmountCurrencyCode | String | The three-character ISO-4217 currency code that identifies the currency. | |
ItemAmountValue | String | The value of item amount. | |
ItemCode | String | An item code that identifies a merchant's goods or service. | |
ItemDescription | String | The item description. | |
ItemName | String | The item name. | |
ItemQuantity | String | The number of purchased units of goods or a service. | |
ItemUnitPriceCurrencyCode | String | The three-character ISO-4217 currency code that identifies the currency. | |
ItemUnitPriceValue | String | The value of item unit price. | |
TaxAmounts | String | An array of tax amounts levied by a government on the purchase of goods or services. | |
TotalItemAmountCurrencyCode | String | The three-character ISO-4217 currency code that identifies the currency. | |
TotalItemAmountValue | String | The value of total item amount. |
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 |
---|---|---|
StartDate | Datetime | |
EndDate | Datetime | |
TransactionId | String | |
PaymentInstrumentType | String | |
BalanceAffectingRecordsOnly | String | |
StoreId | String | |
TerminalId | String | |
TransactionAmount | String | |
TransactionCurrency | String | |
TransactionStatus | String | |
TransactionType | String |
TransactionReportsIncentiveDetails¶
List Incentive Details of Transaction Reports.
Columns¶
Name | Type | References | Description |
---|---|---|---|
AccountNumber | String | The merchant account number. | |
LastRefreshedDatetime | Datetime | The date and time when the data was last refreshed, in Internet date and time format. | |
IncentiveAmountCurrencyCode | String | The three-character ISO-4217 currency code that identifies the currency. | |
IncentiveAmountValue | String | The value of incentive amount. | |
IncentiveCode | String | The code that identifies an incentive, such as a coupon. | |
IncentiveProgramCode | String | The incentive program code that identifies a merchant loyalty or incentive program. | |
IncentiveType | String | The type of incentive, such as a special offer or coupon. |
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 |
---|---|---|
StartDate | Datetime | |
EndDate | Datetime | |
TransactionId | String | |
PaymentInstrumentType | String | |
BalanceAffectingRecordsOnly | String | |
StoreId | String | |
TerminalId | String | |
TransactionAmount | String | |
TransactionCurrency | String | |
TransactionStatus | String | |
TransactionType | String |
Webhooks¶
Query the list of Webhooks.
Table Specific Information¶
Select
The PayPal API does not support any filters on this table. However, projections are supported. All filters are processed client side within the connector.
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The identifier of the webhook resource. | |
Url | String | The webhook notification endpoint URL. |
Stored Procedures¶
Stored procedures are function-like interfaces that extend the functionality of the connector beyond simple SELECT operations with PayPal.
Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from PayPal, along with an indication of whether the procedure succeeded or failed.
PayPal Connector Stored Procedures¶
Name | Description |
---|---|
CreatePayouts | Makes payouts in PayPal. |
CreateSubscription | Create a subscription for PayPal. |
GetOAuthAccessToken | Gets the OAuth access token from PayPal. |
RefreshOAuthAccessToken | Refreshes the OAuth access token used for authentication with various PayPal services. |
SendInvoice | Send an Invoice, by Id, to a customer. |
CreatePayouts¶
Makes payouts in PayPal.
Table Specific Information¶
Payout Items
You cannot create a payout without payout items. To create payout items, you must insert data either using a JSON aggregate or via temporary table called 'PayoutItem#TEMP'. Refer to the following examples for more information.
Example: Create payouts using JSON aggregate
EXECUTE CreatePayouts SenderBatchId = '1247', EmailSubject = 'You have a payout!', EmailMessage = 'You have received a payout! Thanks for using our service!', PayoutItem = '[{\n" +
"\"recipient_type\": \"EMAIL\",\n" +
"\"amount\": {\n" +
"\"value\": \"0.0\",\n" +
"\"currency\": \"USD\"\n" +
"},\n" +
"\"note\": \"Thanks for your patronage3333!\",\n" +
"\"receiver\": \"receiver@example.com\",\n" +
"\"sender_item_id\": \"201403140001\"\n" +
"}, {\n" +
"\"recipient_type\": \"PHONE\",\n" +
"\"amount\": {\n" +
"\"value\": \"0.0\",\n" +
"\"currency\": \"USD\"\n" +
"\t},\n" +
"\"note\": \"Thanks for your support!\",\n" +
"\"receiver\": \"91-734-234-1234\",\n" +
"\"sender_item_id\": \"201403140002\"\n" +
"}\n" +
"]'
Example: Create payouts using temp table
You first need to insert into PayoutItem#TEMP to create payout items.
INSERT INTO PayoutItem#TEMP (RecipientType, AmountValue, AmountCurrency, Note, SenderItemId, Receiver) VALUES ('EMAIL', '3.2', 'USD', 'Thanks for your patronage!', '201403140001', 'receiver@example.com')
INSERT INTO PayoutItem#TEMP (RecipientType, AmountValue, AmountCurrency, Note, SenderItemId, Receiver) VALUES ('PHONE', '8.98', 'USD', 'Thanks for your support!', '201403140002', '91-734-234-1234')
Execute
After creating at least one payout item, you can execute the stored procedure.
EXECUTE CreatePayouts SenderBatchId = '123', EmailSubject = 'You have a payout!', EmailMessage = 'You have received a payout! Thanks for using our service!'
The following columns are required to create a payout item:
- RecipientType
- AmountValue
- AmountCurrency
- SenderItemId
- Receiver
Input
Name | Type | Required | Description |
---|---|---|---|
SenderBatchId | String | True | A sender-specified ID number. Tracks the payout in an accounting system. |
EmailSubject | String | False | The subject line for the email that PayPal sends when payment for a payout item completes. The subject line is the same for all recipients. |
EmailMessage | String | False | The email message that PayPal sends when the payout item completes. The message is the same for all recipients. |
PayoutItems | String | False | A JSON aggregate which contains an array of individual payout items |
Result Set Columns
Name | Type | Description |
---|---|---|
PayoutBatchId | String | The PayPal-generated ID for the payout. |
BatchStatus | String | The PayPal-generated payout status. If the payout passes preliminary checks, the status is PENDING. |
CreateSubscription¶
Create a subscription for PayPal.
Stored Procedure Specific Information¶
The CreateSubscription Stored Procedure requires PlanId to create a PayPal Subscription.
EXECUTE CreateSubscription PlanId = 'P-5ML4271244454362WXNWU5NQ', StartTime = '2021-11-01T00:00:00Z', ShippingAmount = '{\n" +
" \"currency_code\": \"USD\",\n" +
" \"value\": \"10.00\"\n" +
" }', subscriber = '{\n" +
" \"name\": {\n" +
" \"given_name\": \"John\",\n" +
" \"surname\": \"Doe\"\n" +
" },\n" +
" \"email_address\": \"customer@example.com\",\n" +
" \"shipping_address\": {\n" +
" \"name\": {\n" +
" \"full_name\": \"John Doe\"\n" +
" },\n" +
" \"address\": {\n" +
" \"address_line_1\": \"2211 N First Street\",\n" +
" \"address_line_2\": \"Building 17\",\n" +
" \"admin_area_2\": \"San Jose\",\n" +
" \"admin_area_1\": \"CA\",\n" +
" \"postal_code\": \"95131\",\n" +
" \"country_code\": \"US\"\n" +
" }\n" +
" }\n" +
" }'
The following columns are required to create a subscription:
- PlanId
Input
Name | Type | Required | Description |
---|---|---|---|
PlanId | String | True | The ID of the plan.. |
StartTime | String | False | The date and time when the subscription started. |
Quantity | String | False | The quantity of the product in the subscription.. |
ShippingAmount | String | False | A JSON aggregate which contain the shipping charges. |
Subscriber | String | False | A JSON aggregate which contain the subscriber request information . |
ApplicationContext | String | False | A JSON aggregate which contain the application context, which customizes the payer experience during the subscription approval process with PayPal. |
CustomId | String | False | The custom ID for the subscription. Can be invoice id. |
Plan | String | False | A JSON aggregate which contain An inline plan object to customise the subscription. |
Result Set Columns
Name | Type | Description |
---|---|---|
SubscriptionId | String | The PayPal-generated ID for the subscription. |
Status | String | The PayPal-generated subscription status. |
GetOAuthAccessToken¶
Gets the OAuth access token from PayPal.
Result Set Columns¶
Name | Type | Description |
---|---|---|
OAuthAccessToken | String | The OAuth token. |
OAuthRefreshToken | String | The OAuth refresh token. |
ExpiresIn | String | The remaining lifetime for the access token in seconds. |
RefreshOAuthAccessToken¶
Refreshes the OAuth access token used for authentication with various PayPal services.
Result Set Columns¶
Name | Type | Description |
---|---|---|
OAuthAccessToken | String | The authentication token returned from PayPal. This can be used in subsequent calls to other operations for this particular service. |
OAuthRefreshToken | String | A token that may be used to obtain a new access token. |
ExpiresIn | String | The remaining lifetime on the access token. |
SendInvoice¶
Send an Invoice, by Id, to a customer.
Input¶
Name | Type | Required | Description |
---|---|---|---|
InvoiceId | String | True | The ID of the invoice to send. |
Subject | String | False | The subject of the email that is sent as a notification to the recipient. |
Note | String | False | A note to the payer. |
SendToInvoicer | Boolean | False | Indicates whether to send a copy of the email to the merchant. |
SendToRecipient | Boolean | False | Indicates whether to send a copy of the email to the recipient. |
AdditionalRecipients | String | False | An array of one or more CC: emails to which notifications are sent. If you omit this parameter, a notification is sent to all CC: email addresses that are part of the invoice. |
SOAP Data Model¶
The PayPal connector models the PayPal SOAP APIs as database Views. Typically, read-only data are shown as views. These are defined in schema files, which are simple, text-based configuration files that make schemas easy to customize.
This section provides the API limitations and requirements for the SOAP data model; you can use the SupportEnhancedSQL
feature, set by default, to circumvent most of these limitations.
Views¶
Views are tables that cannot be modified. Typically, read-only data are shown as views.
Views¶
Views are similar to tables in the way that data is represented; however, views are read-only.
Queries can be executed against a view as if it were a normal table.
PayPal Connector Views¶
Name | Description |
---|---|
Balance | View all the currencies of the account and their balances. |
BalanceAffecting | Query Transactions that affect the account balance. |
BalanceTransfer | Query Transactions involving Balance Transfers. |
BillPay | Query Transactions involving BillPay Transactions. |
CurrencyConversions | Query Transactions involving Currency Conversions. |
Dividend | Query Transactions involving Dividends. |
ECheck | Query Transactions involving eChecks. |
Fee | Query Transactions involving Fees. |
FundsAdded | Query Transactions generated when funds are added to the balance. |
FundsWithdrawn | Query Transactions generated when funds are withdrawn from the balance. |
MassPay | Query Mass Payments Transactions. |
MoneyRequest | Query Money Request Transactions. |
Received | Query payments-received Transactions. |
Referral | Query Transactions involving Referrals. |
Refund | Query Transactions involving Refunds. |
Reversal | Query Transactions involving BillPay Reversals. |
Sent | Query payments-sent Transactions. |
Shipping | Query Transactions involving UPS Shipping Fees. |
Subscription | Query Transactions involving Subscriptions. |
TransactionDetails | Query details about a specific Transaction using the GetTransactionDetails API. |
TransactionItems | Query details about a specific Transaction's items using the GetTransactionDetails API. |
Transactions | Query Transaction history using the TransactionSearch API. |
Balance¶
View all the currencies of the account and their balances.
Columns¶
Name | Type | References | Description |
---|---|---|---|
CurrencyID | String | The currency of the balance. | |
Balance | Double | The balance of the associated currency. |
BalanceAffecting¶
Query Transactions that affect the account balance.
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The ID of the transaction record of the merchant. | |
PayerDisplayName | String | The display name of the buyer. | |
NetAmount | Decimal | The net amount of the transaction. | |
FeeAmount | Decimal | The fee that PayPal charged for the transaction. | |
GrossAmount | Decimal | The total gross amount charged, including any profile shipping cost and taxes. | |
Status | String | The status of the transaction. This field is one of the following values: Pending, Processing, Success, Denied, or Reversed. The allowed values are Pending, Processing, Success, Denied, Reversed. | |
Date | Datetime | Date and time that the transaction occurred. Character length and limitations: The date and time of the transaction. | |
Timezone | String | The time zone of the transaction. The value is always GMT. | |
Type | String | The type of the transaction. It is one of the following values: Currency Conversion (credit), Currency Conversion (debit), Payment, Recurring Payment, Temporary Hold, Transfer, Donation. | |
Payer | String | The email address of the buyer. Character length and limitations: 127 single-byte alphanumeric characters. |
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 |
---|---|---|
Amount | String | |
AuctionItemNumber | String | |
CardNumber | String | |
CurrencyCode | String | |
InvoiceId | String | |
ProfileId | String | |
ReceiptId | String | |
Receiver | String | |
PayerName | String |
BalanceTransfer¶
Query Transactions involving Balance Transfers.
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The ID of the transaction record of the merchant. | |
PayerDisplayName | String | The display name of the buyer. | |
NetAmount | Decimal | The net amount of the transaction. | |
FeeAmount | Decimal | The fee that PayPal charged for the transaction. | |
GrossAmount | Decimal | The total gross amount charged, including any profile shipping cost and taxes. | |
Status | String | The status of the transaction. This field is one of the following values: Pending, Processing, Success, Denied, or Reversed. The allowed values are Pending, Processing, Success, Denied, Reversed. | |
Date | Datetime | The date and time that the transaction occurred. | |
Timezone | String | The time zone of the transaction. The value is always GMT. | |
Type | String | The type of the transaction. It is one of the following values: Currency Conversion (credit), Currency Conversion (debit), Payment, Recurring Payment, Temporary Hold, Transfer, or Donation. | |
Payer | String | The email address of the buyer. Character length and limitations: 127 single-byte alphanumeric characters. |
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 |
---|---|---|
Amount | String | |
AuctionItemNumber | String | |
CardNumber | String | |
CurrencyCode | String | |
InvoiceId | String | |
ProfileId | String | |
ReceiptId | String | |
Receiver | String | |
PayerName | String |
BillPay¶
Query Transactions involving BillPay Transactions.
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The ID of the transaction record of the merchant. | |
PayerDisplayName | String | The display name of the buyer. | |
NetAmount | Decimal | The net amount of the transaction. | |
FeeAmount | Decimal | The fee that PayPal charged for the transaction. | |
GrossAmount | Decimal | The total gross amount charged, including any profile shipping cost and taxes. | |
Status | String | The status of the transaction. This field is one of the following values: Pending, Processing, Success, Denied, or Reversed. The allowed values are Pending, Processing, Success, Denied, Reversed. | |
Date | Datetime | The date and time that the transaction occurred. | |
Timezone | String | The time zone of the transaction. The value is always GMT. | |
Type | String | The type of the transaction. It is one of the following values: Currency Conversion (credit), Currency Conversion (debit), Payment, Recurring Payment, Temporary Hold, Transfer, Donation. | |
Payer | String | The email address of the buyer. Character length and limitations: 127 single-byte alphanumeric characters. |
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 |
---|---|---|
Amount | String | |
AuctionItemNumber | String | |
CardNumber | String | |
CurrencyCode | String | |
InvoiceId | String | |
ProfileId | String | |
ReceiptId | String | |
Receiver | String | |
PayerName | String |
CurrencyConversions¶
Query Transactions involving Currency Conversions.
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The ID of the transaction record of the merchant. | |
PayerDisplayName | String | The display name of the buyer. | |
NetAmount | Decimal | The net amount of the transaction. | |
FeeAmount | Decimal | The fee that PayPal charged for the transaction. | |
GrossAmount | Decimal | The total gross amount charged, including any profile shipping cost and taxes. | |
Status | String | The status of the transaction. This field is one of the following values: Pending, Processing, Success, Denied, or Reversed. The allowed values are Pending, Processing, Success, Denied, Reversed. | |
Date | Datetime | The date and time that the transaction occurred. | |
Timezone | String | The time zone of the transaction. The value is always GMT. | |
Type | String | The type of the transaction. It is one of the following values: Currency Conversion (credit), Currency Conversion (debit), Payment, Recurring Payment, Temporary Hold, Transfer, Donation. | |
Payer | String | The email address of the buyer. Character length and limitations: 127 single-byte alphanumeric characters. |
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 |
---|---|---|
Amount | String | |
AuctionItemNumber | String | |
CardNumber | String | |
CurrencyCode | String | |
InvoiceId | String | |
ProfileId | String | |
ReceiptId | String | |
Receiver | String | |
PayerName | String |
Dividend¶
Query Transactions involving Dividends.
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The ID of the transaction record of the merchant. | |
PayerDisplayName | String | The display name of the buyer. | |
NetAmount | Decimal | The net amount of the transaction. | |
FeeAmount | Decimal | The fee that PayPal charged for the transaction. | |
GrossAmount | Decimal | The total gross amount charged, including any profile shipping cost and taxes. | |
Status | String | The status of the transaction. This field is one of the following values: Pending, Processing, Success, Denied, or Reversed. The allowed values are Pending, Processing, Success, Denied, Reversed. | |
Date | Datetime | The date and time that the transaction occurred. | |
Timezone | String | The time zone of the transaction. The value is always GMT. | |
Type | String | The type of the transaction. It is one of the following values: Currency Conversion (credit), Currency Conversion (debit), Payment, Recurring Payment, Temporary Hold, Transfer, Donation. | |
Payer | String | The email address of the buyer. Character length and limitations: 127 single-byte alphanumeric characters. |
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 |
---|---|---|
Amount | String | |
AuctionItemNumber | String | |
CardNumber | String | |
CurrencyCode | String | |
InvoiceId | String | |
ProfileId | String | |
ReceiptId | String | |
Receiver | String | |
PayerName | String |
ECheck¶
Query Transactions involving eChecks.
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The ID of the transaction record of the merchant. | |
PayerDisplayName | String | The display name of the buyer. | |
NetAmount | Decimal | The net amount of the transaction. | |
FeeAmount | Decimal | The fee that PayPal charged for the transaction. | |
GrossAmount | Decimal | The total gross amount charged, including any profile shipping cost and taxes. | |
Status | String | The status of the transaction. This field is one of the following values: Pending, Processing, Success, Denied, or Reversed. The allowed values are Pending, Processing, Success, Denied, Reversed. | |
Date | Datetime | The date and time that the transaction occurred. | |
Timezone | String | The time zone of the transaction. The value is always GMT. | |
Type | String | The type of the transaction. It is one of the following values: Currency Conversion (credit), Currency Conversion (debit), Payment, Recurring Payment, Temporary Hold, Transfer, Donation. | |
Payer | String | The email address of the buyer. Character length and limitations: 127 single-byte alphanumeric characters. |
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 |
---|---|---|
Amount | String | |
AuctionItemNumber | String | |
CardNumber | String | |
CurrencyCode | String | |
InvoiceId | String | |
ProfileId | String | |
ReceiptId | String | |
Receiver | String | |
PayerName | String |
Fee¶
Query Transactions involving Fees.
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The ID of the transaction record of the merchant. | |
PayerDisplayName | String | The display name of the buyer. | |
NetAmount | Decimal | The net amount of the transaction. | |
FeeAmount | Decimal | The fee that PayPal charged for the transaction. | |
GrossAmount | Decimal | The total gross amount charged, including any profile shipping cost and taxes. | |
Status | String | The status of the transaction. This field is one of the following values: Pending, Processing, Success, Denied, or Reversed. The allowed values are Pending, Processing, Success, Denied, Reversed. | |
Date | Datetime | The date and time that the transaction occurred. | |
Timezone | String | The time zone of the transaction. The value is always GMT. | |
Type | String | The type of the transaction. It is one of the following values: Currency Conversion (credit), Currency Conversion (debit), Payment, Recurring Payment, Temporary Hold, Transfer, Donation. | |
Payer | String | The email address of the buyer. Character length and limitations: 127 single-byte alphanumeric characters. |
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 |
---|---|---|
Amount | String | |
AuctionItemNumber | String | |
CardNumber | String | |
CurrencyCode | String | |
InvoiceId | String | |
ProfileId | String | |
ReceiptId | String | |
Receiver | String | |
PayerName | String |
FundsAdded¶
Query Transactions generated when funds are added to the balance.
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The ID of the transaction record of the merchant. | |
PayerDisplayName | String | The display name of the buyer. | |
NetAmount | Decimal | The net amount of the transaction. | |
FeeAmount | Decimal | The fee that PayPal charged for the transaction. | |
GrossAmount | Decimal | The total gross amount charged, including any profile shipping cost and taxes. | |
Status | String | The status of the transaction. This field is one of the following values: Pending, Processing, Success, Denied, or Reversed. The allowed values are Pending, Processing, Success, Denied, Reversed. | |
Date | Datetime | The date and time that the transaction occurred. | |
Timezone | String | The time zone of the transaction. The value is always GMT. | |
Type | String | The type of the transaction. It is one of the following values: Currency Conversion (credit), Currency Conversion (debit), Payment, Recurring Payment, Temporary Hold, Transfer, Donation. | |
Payer | String | The email address of the buyer. Character length and limitations: 127 single-byte alphanumeric characters. |
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 |
---|---|---|
Amount | String | |
AuctionItemNumber | String | |
CardNumber | String | |
CurrencyCode | String | |
InvoiceId | String | |
ProfileId | String | |
ReceiptId | String | |
Receiver | String | |
PayerName | String |
FundsWithdrawn¶
Query Transactions generated when funds are withdrawn from the balance.
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The ID of the transaction record of the merchant. | |
PayerDisplayName | String | The display name of the buyer. | |
NetAmount | Decimal | The net amount of the transaction. | |
FeeAmount | Decimal | The fee that PayPal charged for the transaction. | |
GrossAmount | Decimal | The total gross amount charged, including any profile shipping cost and taxes. | |
Status | String | The status of the transaction. This field is one of the following values: Pending, Processing, Success, Denied, or Reversed. The allowed values are Pending, Processing, Success, Denied, Reversed. | |
Date | Datetime | The date and time that the transaction occurred. | |
Timezone | String | The time zone of the transaction. The value is always GMT. | |
Type | String | The type of the transaction. It is one of the following values: Currency Conversion (credit), Currency Conversion (debit), Payment, Recurring Payment, Temporary Hold, Transfer, Donation. | |
Payer | String | The email address of the buyer. Character length and limitations: 127 single-byte alphanumeric characters. |
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 |
---|---|---|
Amount | String | |
AuctionItemNumber | String | |
CardNumber | String | |
CurrencyCode | String | |
InvoiceId | String | |
ProfileId | String | |
ReceiptId | String | |
Receiver | String | |
PayerName | String |
MassPay¶
Query Mass Payments Transactions.
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The ID of the transaction record of the merchant. | |
PayerDisplayName | String | The display name of the buyer. | |
NetAmount | Decimal | The net amount of the transaction. | |
FeeAmount | Decimal | The fee that PayPal charged for the transaction. | |
GrossAmount | Decimal | The total gross amount charged, including any profile shipping cost and taxes. | |
Status | String | The status of the transaction. This field is one of the following values: Pending, Processing, Success, Denied, or Reversed. The allowed values are Pending, Processing, Success, Denied, Reversed. | |
Date | Datetime | The date and time that the transaction occurred. | |
Timezone | String | The time zone of the transaction. The value is always GMT. | |
Type | String | The type of the transaction. It is one of the following values: Currency Conversion (credit), Currency Conversion (debit), Payment, Recurring Payment, Temporary Hold, Transfer, Donation. | |
Payer | String | The email address of the buyer. Character length and limitations: 127 single-byte alphanumeric characters. |
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 |
---|---|---|
Amount | String | |
AuctionItemNumber | String | |
CardNumber | String | |
CurrencyCode | String | |
InvoiceId | String | |
ProfileId | String | |
ReceiptId | String | |
Receiver | String | |
PayerName | String |
MoneyRequest¶
Query Money Request Transactions.
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The ID of the transaction record of the merchant. | |
PayerDisplayName | String | The display name of the buyer. | |
NetAmount | Decimal | The net amount of the transaction. | |
FeeAmount | Decimal | The fee that PayPal charged for the transaction. | |
GrossAmount | Decimal | The total gross amount charged, including any profile shipping cost and taxes. | |
Status | String | The status of the transaction. This field is one of the following values: Pending, Processing, Success, Denied, or Reversed. The allowed values are Pending, Processing, Success, Denied, Reversed. | |
Date | Datetime | The date and time that the transaction occurred. | |
Timezone | String | The time zone of the transaction. The value is always GMT. | |
Type | String | The type of the transaction. It is one of the following values: Currency Conversion (credit), Currency Conversion (debit), Payment, Recurring Payment, Temporary Hold, Transfer, Donation. | |
Payer | String | The email address of the buyer. Character length and limitations: 127 single-byte alphanumeric characters. |
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 |
---|---|---|
Amount | String | |
AuctionItemNumber | String | |
CardNumber | String | |
CurrencyCode | String | |
InvoiceId | String | |
ProfileId | String | |
ReceiptId | String | |
Receiver | String | |
PayerName | String |
Received¶
Query payments-received Transactions.
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The ID of the transaction record of the merchant. | |
PayerDisplayName | String | The display name of the buyer. | |
NetAmount | Decimal | The net amount of the transaction. | |
FeeAmount | Decimal | The fee that PayPal charged for the transaction. | |
GrossAmount | Decimal | The total gross amount charged, including any profile shipping cost and taxes. | |
Status | String | The status of the transaction. This field is one of the following values: Pending, Processing, Success, Denied, or Reversed. The allowed values are Pending, Processing, Success, Denied, Reversed. | |
Date | Datetime | The date and time that the transaction occurred. | |
Timezone | String | The time zone of the transaction. The value is always GMT. | |
Type | String | The type of the transaction. It is one of the following values: Currency Conversion (credit), Currency Conversion (debit), Payment, Recurring Payment, Temporary Hold, Transfer, Donation. | |
Payer | String | The email address of the buyer. Character length and limitations: 127 single-byte alphanumeric characters. |
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 |
---|---|---|
Amount | String | |
AuctionItemNumber | String | |
CardNumber | String | |
CurrencyCode | String | |
InvoiceId | String | |
ProfileId | String | |
ReceiptId | String | |
Receiver | String | |
PayerName | String |
Referral¶
Query Transactions involving Referrals.
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The ID of the transaction record of the merchant. | |
PayerDisplayName | String | The display name of the buyer. | |
NetAmount | Decimal | The net amount of the transaction. | |
FeeAmount | Decimal | The fee that PayPal charged for the transaction. | |
GrossAmount | Decimal | The total gross amount charged, including any profile shipping cost and taxes. | |
Status | String | The status of the transaction. This field is one of the following values: Pending, Processing, Success, Denied, or Reversed. The allowed values are Pending, Processing, Success, Denied, Reversed. | |
Date | Datetime | The date and time that the transaction occurred. | |
Timezone | String | The time zone of the transaction. The value is always GMT. | |
Type | String | The type of the transaction. It is one of the following values: Currency Conversion (credit), Currency Conversion (debit), Payment, Recurring Payment, Temporary Hold, Transfer, Donation. | |
Payer | String | The email address of the buyer. Character length and limitations: 127 single-byte alphanumeric characters. |
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 |
---|---|---|
Amount | String | |
AuctionItemNumber | String | |
CardNumber | String | |
CurrencyCode | String | |
InvoiceId | String | |
ProfileId | String | |
ReceiptId | String | |
Receiver | String | |
PayerName | String |
Refund¶
Query Transactions involving Refunds.
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The ID of the transaction record of the merchant. | |
PayerDisplayName | String | The display name of the buyer. | |
NetAmount | Decimal | The net amount of the transaction. | |
FeeAmount | Decimal | The fee that PayPal charged for the transaction. | |
GrossAmount | Decimal | The total gross amount charged, including any profile shipping cost and taxes. | |
Status | String | The status of the transaction. This field is one of the following values: Pending, Processing, Success, Denied, or Reversed. The allowed values are Pending, Processing, Success, Denied, Reversed. | |
Date | Datetime | The date and time that the transaction occurred. | |
Timezone | String | The time zone of the transaction. The value is always GMT. | |
Type | String | The type of the transaction. It is one of the following values: Currency Conversion (credit), Currency Conversion (debit), Payment, Recurring Payment, Temporary Hold, Transfer, Donation. | |
Payer | String | The email address of the buyer. Character length and limitations: 127 single-byte alphanumeric characters. |
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 |
---|---|---|
Amount | String | |
AuctionItemNumber | String | |
CardNumber | String | |
CurrencyCode | String | |
InvoiceId | String | |
ProfileId | String | |
ReceiptId | String | |
Receiver | String | |
PayerName | String |
Reversal¶
Query Transactions involving BillPay Reversals.
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The ID of the transaction record of the merchant. | |
PayerDisplayName | String | The display name of the buyer. | |
NetAmount | Decimal | The net amount of the transaction. | |
FeeAmount | Decimal | The fee that PayPal charged for the transaction. | |
GrossAmount | Decimal | The total gross amount charged, including any profile shipping cost and taxes. | |
Status | String | The status of the transaction. This field is one of the following values: Pending, Processing, Success, Denied, or Reversed. The allowed values are Pending, Processing, Success, Denied, Reversed. | |
Date | Datetime | The date and time that the transaction occurred. | |
Timezone | String | The time zone of the transaction. The value is always GMT. | |
Type | String | The type of the transaction. It is one of the following values: Currency Conversion (credit), Currency Conversion (debit), Payment, Recurring Payment, Temporary Hold, Transfer, Donation. | |
Payer | String | The email address of the buyer. Character length and limitations: 127 single-byte alphanumeric characters. |
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 |
---|---|---|
Amount | String | |
AuctionItemNumber | String | |
CardNumber | String | |
CurrencyCode | String | |
InvoiceId | String | |
ProfileId | String | |
ReceiptId | String | |
Receiver | String | |
PayerName | String |
Sent¶
Query payments-sent Transactions.
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The ID of the transaction record of the merchant. | |
PayerDisplayName | String | The display name of the buyer. | |
NetAmount | Decimal | The net amount of the transaction. | |
FeeAmount | Decimal | The fee that PayPal charged for the transaction. | |
GrossAmount | Decimal | The total gross amount charged, including any profile shipping cost and taxes. | |
Status | String | The status of the transaction. This field is one of the following values: Pending, Processing, Success, Denied, or Reversed. The allowed values are Pending, Processing, Success, Denied, Reversed. | |
Date | Datetime | The date and time that the transaction occurred. | |
Timezone | String | The time zone of the transaction. The value is always GMT. | |
Type | String | The type of the transaction. It is one of the following values: Currency Conversion (credit), Currency Conversion (debit), Payment, Recurring Payment, Temporary Hold, Transfer, Donation. | |
Payer | String | The email address of the buyer. Character length and limitations: 127 single-byte alphanumeric characters. |
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 |
---|---|---|
Amount | String | |
AuctionItemNumber | String | |
CardNumber | String | |
CurrencyCode | String | |
InvoiceId | String | |
ProfileId | String | |
ReceiptId | String | |
Receiver | String | |
PayerName | String |
Shipping¶
Query Transactions involving UPS Shipping Fees.
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The ID of the transaction record of the merchant. | |
PayerDisplayName | String | The display name of the buyer. | |
NetAmount | Decimal | The net amount of the transaction. | |
FeeAmount | Decimal | The fee that PayPal charged for the transaction. | |
GrossAmount | Decimal | The total gross amount charged, including any profile shipping cost and taxes. | |
Status | String | The status of the transaction. This field is one of the following values: Pending, Processing, Success, Denied, or Reversed. The allowed values are Pending, Processing, Success, Denied, Reversed. | |
Date | Datetime | The date and time that the transaction occurred. | |
Timezone | String | The time zone of the transaction. The value is always GMT. | |
Type | String | The type of the transaction. It is one of the following values: Currency Conversion (credit), Currency Conversion (debit), Payment, Recurring Payment, Temporary Hold, Transfer, Donation. | |
Payer | String | The email address of the buyer. Character length and limitations: 127 single-byte alphanumeric characters. |
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 |
---|---|---|
Amount | String | |
AuctionItemNumber | String | |
CardNumber | String | |
CurrencyCode | String | |
InvoiceId | String | |
ProfileId | String | |
ReceiptId | String | |
Receiver | String | |
PayerName | String |
Subscription¶
Query Transactions involving Subscriptions.
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The ID of the transaction record of the merchant. | |
PayerDisplayName | String | The display name of the buyer. | |
NetAmount | Decimal | The net amount of the transaction. | |
FeeAmount | Decimal | The fee that PayPal charged for the transaction. | |
GrossAmount | Decimal | The total gross amount charged, including any profile shipping cost and taxes. | |
Status | String | The status of the transaction. This field is one of the following values: Pending, Processing, Success, Denied, or Reversed. The allowed values are Pending, Processing, Success, Denied, Reversed. | |
Date | Datetime | The date and time that the transaction occurred. | |
Timezone | String | The time zone of the transaction. The value is always GMT. | |
Type | String | The type of the transaction. It is one of the following values: Currency Conversion (credit), Currency Conversion (debit), Payment, Recurring Payment, Temporary Hold, Transfer, Donation. | |
Payer | String | The email address of the buyer. Character length and limitations: 127 single-byte alphanumeric characters. |
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 |
---|---|---|
Amount | String | |
AuctionItemNumber | String | |
CardNumber | String | |
CurrencyCode | String | |
InvoiceId | String | |
ProfileId | String | |
ReceiptId | String | |
Receiver | String | |
PayerName | String |
TransactionDetails¶
Query details about a specific Transaction using the GetTransactionDetails API.
Table Specific Information¶
Select
The only filter supported by the PayPal API is TransactionId, which is required. The connector processes other filters client side within the connector.
For example, the following queries are processed server side and retrieve details for a specific Transaction.
Warning
: Requesting all TransactionDetails for all transactions could take a long time depending on the number of transactions.
SELECT * FROM TransactionDetails WHERE TransactionId = '4W92068757818534U'
SELECT * FROM TransactionDetails WHERE TransactionId IN ('4W92068757818534U', '0TM835405G491863M')
SELECT * FROM TransactionDetails WHERE TransactionId IN (SELECT ID FROM Transactions)
Columns¶
Name | Type | References | Description |
---|---|---|---|
TransactionId [KEY] | String | Unique transaction ID of the payment. | |
ReceiverBusiness | String | Details about a single transaction. This field is not applicable for point-of-sale transactions. | |
ReceiverEmail | String | Primary email address of the payment recipient (the merchant). | |
ReceiverId | String | Unique account ID of the payment recipient (the merchant). This value is the same as the value of the referral ID of the recipient. | |
PayerEmail | String | Email address of the buyer. | |
PayerId | String | Unique PayPal Customer Account identification number. | |
PayerStatus | String | Status of the buyer. | |
PayerFirstName | String | The first name of the buyer. | |
PayerMiddleName | String | The middle name of the buyer. | |
PayerLastName | String | The last name of the buyer. | |
PayerSuffix | String | The suffix of the buyer. | |
PayerCountry | String | The country of residence of the buyer in the form of ISO standard 3166 2-character country codes. | |
PayerBusiness | String | The business name of the buyer. | |
StreetAddress | String | The first street address. | |
City | String | The name of the city. | |
State | String | The state or province. | |
PostalCode | String | U.S. ZIP code or other country-specific postal code. | |
ShippingCountry | String | The expanded name of the country. | |
PayerPhone | String | The phone number. | |
ParentTransactionId | String | The parent or related transaction identification number. | |
TransactionType | String | The type of transaction. | |
PaymentType | String | Indicates whether the payment is instant or delayed. | |
PaymentDate | Datetime | The date-time stamp of the payment. | |
GrossAmount | Decimal | The final amount charged, including any shipping and taxes from your Merchant Profile. | |
FeeAmount | Decimal | The PayPal fee amount charged for the transaction. | |
SettleAmount | Decimal | The amount deposited in your PayPal account after a currency conversion. | |
TaxAmount | Decimal | The tax charged on the transaction. | |
ExchangeRate | String | The exchange rate if a currency conversion occurred. This is relevant only if you are billing in the non-primary currency of the buyer. If the buyer chooses to pay with a currency other than the non-primary currency, the conversion occurs in the account of the buyer. | |
PaymentStatus | String | Status of the payment. | |
PendingReason | String | The reason the payment is pending. | |
ProtectionEligibility | String | Prior to version 64.4, the kind of seller protection in force for the transaction. | |
ProtectionEligibilityType | String | Since version 64.4, the kind of seller protection in force for the transaction. | |
StoreId | String | StoreId as entered in the transaction. | |
Subject | String | The value of the subject field entered by the buyer when making a payment. | |
InvoiceId | String | Invoice number you set in the original transaction. | |
Memo | String | Memo entered by your customer in the PayPal Website Payments note field. | |
SalesTax | Decimal | Amount of tax charged on payment. | |
AuctionBuyerId | String | The auction ID of the buyer. | |
AuctionClosingDate | Datetime | The close date of the auction. |
TransactionItems¶
Query details about a specific Transaction's items using the GetTransactionDetails API.
View Specific Information¶
Select
The only filter supported by the PayPal API is TransactionId, which is required. The connector processes other filters client side within the connector.
For example, the following queries are processed server side and retrieve details for a specific Transaction.
Warning
: Requesting all TransactionItems for all transactions could take a long time depending on the number of transactions.
SELECT * FROM TransactionItems WHERE TransactionId = '4W92068757818534U'
SELECT * FROM TransactionItems WHERE TransactionId IN ('4W92068757818534U', '0TM835405G491863M')
SELECT * FROM TransactionItems WHERE TransactionId IN (SELECT ID FROM Transactions)
Columns¶
Name | Type | References | Description |
---|---|---|---|
TransactionID | String | Unique transaction ID of the payment. | |
EbayItemTxnId | String | The eBay auction transaction ID of the item that you use to identify items that the buyer purchased. | |
ItemName | String | Item name set by you or entered by the customer. | |
ItemNumber | Integer | Item number set by you. If this was a shopping cart transaction, PayPal appends the number of the item to the HTML item_number variable, for example, item_number1, item_number2, and so forth. | |
ShippingAmount | Decimal | Shipping Amount. | |
HandlingAmount | Decimal | Handling Amount. | |
Quantity | Integer | Quantity set by you or entered by the buyer. | |
CouponId | String | Coupon identification number. | |
CouponAmount | Decimal | Amount (value) of the coupon. | |
ItemAmount | Decimal | Cost of the item. | |
CouponAmountCurrency | String | Currency of the coupon amount, e.g., a 3-character currency code. | |
SalesTax | Decimal | Amount of tax charged on payment for this item. |
Transactions¶
Query Transaction history using the TransactionSearch API.
Table Specific Information¶
Select
The following filters are supported by the PayPal API:
- Id
- Date
- Receiver
- ReceiptId
- AuctionItemNumber
- TransactionClass
The Date filter supports the >, >=, <, <=, and = operators while the other filters only support the = operator.
The connector processes other filters client side within the connector.
For example, the following query is processed server side and retrieves Transactions that were created after the specified Date.
SELECT * FROM Transactions WHERE Date > '2015-12-15T12:00:00' AND Amount = '11'
Other important filter you can execute client-side is the Type filter. You can use that column to retrive i.e: only the Orders.
After retrieving the IDs of transactions with type order, you can use these IDs to query TransactionItems and TransactionDetails views, to get more details.
SELECT * FROM Transactions WHERE Type = 'Order'
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The ID of the transaction record of the merchant. | |
PayerDisplayName | String | The display name of the buyer. | |
NetAmount | Decimal | The net amount of the transaction. | |
FeeAmount | Decimal | The fee that PayPal charged for the transaction. | |
GrossAmount | Decimal | The total gross amount charged, including any profile shipping cost and taxes. | |
Status | String | The status of the transaction. It is one of the following values: Pending, Processing, Success, Denied, or Reversed. The allowed values are Pending, Processing, Success, Denied, Reversed. | |
Date | Datetime | Date and time that the transaction occurred. Character length and limitations: Date and time are in UTC/GMT format; for example, 2013-08-24T05:38:48Z. | |
Timezone | String | The time zone of the transaction. The value is always GMT. | |
Type | String | The type of the transaction. It is one of the following values: Order, Currency Conversion (credit), Currency Conversion (debit), Payment, Recurring Payment, Temporary Hold, Transfer, Donation. | |
Payer | String | Search by the email address of the buyer. Character length and limitations: 127 single-byte alphanumeric characters. |
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 |
---|---|---|
TransactionClass | String | |
Amount | String | |
AuctionItemNumber | String | |
CardNumber | String | |
CurrencyCode | String | |
InvoiceId | String | |
ProfileId | String | |
ReceiptId | String | |
Receiver | String | |
PayerName | String |
System Tables¶
You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.
Schema Tables¶
The following tables return database metadata for PayPal:
- sys_catalogs: Lists the available databases.
- sys_schemas: Lists the available schemas.
- sys_tables: Lists the available tables and views.
- sys_tablecolumns: Describes the columns of the available tables and views.
- sys_procedures: Describes the available stored procedures.
- sys_procedureparameters: Describes stored procedure parameters.
- sys_keycolumns: Describes the primary and foreign keys.
- sys_indexes: Describes the available indexes.
Data Source Tables¶
The following tables return information about how to connect to and query the data source:
- sys_connection_props: Returns information on the available connection properties.
- sys_sqlinfo: Describes the SELECT queries that the connector can offload to the data source.
Query Information Tables¶
The following table returns query statistics for data modification queries:
- sys_identity: Returns information about batch operations or single updates.
sys_catalogs¶
Lists the available databases.
The following query retrieves all databases determined by the connection string:
SELECT * FROM sys_catalogs
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The database name. |
sys_schemas¶
Lists the available schemas.
The following query retrieves all available schemas:
SELECT * FROM sys_schemas
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The database name. |
SchemaName | String | The schema name. |
sys_tables¶
Lists the available tables.
The following query retrieves the available tables and views:
SELECT * FROM sys_tables
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The database containing the table or view. |
SchemaName | String | The schema containing the table or view. |
TableName | String | The name of the table or view. |
TableType | String | The table type (table or view). |
Description | String | A description of the table or view. |
IsUpdateable | Boolean | Whether the table can be updated. |
sys_tablecolumns¶
Describes the columns of the available tables and views.
The following query returns the columns and data types for the Payments table:
SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Payments'
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the table or view. |
SchemaName | String | The schema containing the table or view. |
TableName | String | The name of the table or view containing the column. |
ColumnName | String | The column name. |
DataTypeName | String | The data type name. |
DataType | Int32 | An integer indicating the data type. This value is determined at run time based on the environment. |
Length | Int32 | The storage size of the column. |
DisplaySize | Int32 | The designated column's normal maximum width in characters. |
NumericPrecision | Int32 | The maximum number of digits in numeric data. The column length in characters for character and date-time data. |
NumericScale | Int32 | The column scale or number of digits to the right of the decimal point. |
IsNullable | Boolean | Whether the column can contain null. |
Description | String | A brief description of the column. |
Ordinal | Int32 | The sequence number of the column. |
IsAutoIncrement | String | Whether the column value is assigned in fixed increments. |
IsGeneratedColumn | String | Whether the column is generated. |
IsHidden | Boolean | Whether the column is hidden. |
IsArray | Boolean | Whether the column is an array. |
IsReadOnly | Boolean | Whether the column is read-only. |
IsKey | Boolean | Indicates whether a field returned from sys_tablecolumns is the primary key of the table. |
sys_procedures¶
Lists the available stored procedures.
The following query retrieves the available stored procedures:
SELECT * FROM sys_procedures
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The database containing the stored procedure. |
SchemaName | String | The schema containing the stored procedure. |
ProcedureName | String | The name of the stored procedure. |
Description | String | A description of the stored procedure. |
ProcedureType | String | The type of the procedure, such as PROCEDURE or FUNCTION. |
sys_procedureparameters¶
Describes stored procedure parameters.
The following query returns information about all of the input parameters for the SelectEntries stored procedure:
SELECT * FROM sys_procedureparameters WHERE ProcedureName='SelectEntries' AND Direction=1 OR Direction=2
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the stored procedure. |
SchemaName | String | The name of the schema containing the stored procedure. |
ProcedureName | String | The name of the stored procedure containing the parameter. |
ColumnName | String | The name of the stored procedure parameter. |
Direction | Int32 | An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters. |
DataTypeName | String | The name of the data type. |
DataType | Int32 | An integer indicating the data type. This value is determined at run time based on the environment. |
Length | Int32 | The number of characters allowed for character data. The number of digits allowed for numeric data. |
NumericPrecision | Int32 | The maximum precision for numeric data. The column length in characters for character and date-time data. |
NumericScale | Int32 | The number of digits to the right of the decimal point in numeric data. |
IsNullable | Boolean | Whether the parameter can contain null. |
IsRequired | Boolean | Whether the parameter is required for execution of the procedure. |
IsArray | Boolean | Whether the parameter is an array. |
Description | String | The description of the parameter. |
Ordinal | Int32 | The index of the parameter. |
sys_keycolumns¶
Describes the primary and foreign keys.
The following query retrieves the primary key for the Payments table:
SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Payments'
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the key. |
SchemaName | String | The name of the schema containing the key. |
TableName | String | The name of the table containing the key. |
ColumnName | String | The name of the key column. |
IsKey | Boolean | Whether the column is a primary key in the table referenced in the TableName field. |
IsForeignKey | Boolean | Whether the column is a foreign key referenced in the TableName field. |
PrimaryKeyName | String | The name of the primary key. |
ForeignKeyName | String | The name of the foreign key. |
ReferencedCatalogName | String | The database containing the primary key. |
ReferencedSchemaName | String | The schema containing the primary key. |
ReferencedTableName | String | The table containing the primary key. |
ReferencedColumnName | String | The column name of the primary key. |
sys_foreignkeys¶
Describes the foreign keys.
The following query retrieves all foreign keys which refer to other tables:
SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the key. |
SchemaName | String | The name of the schema containing the key. |
TableName | String | The name of the table containing the key. |
ColumnName | String | The name of the key column. |
PrimaryKeyName | String | The name of the primary key. |
ForeignKeyName | String | The name of the foreign key. |
ReferencedCatalogName | String | The database containing the primary key. |
ReferencedSchemaName | String | The schema containing the primary key. |
ReferencedTableName | String | The table containing the primary key. |
ReferencedColumnName | String | The column name of the primary key. |
ForeignKeyType | String | Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key. |
sys_primarykeys¶
Describes the primary keys.
The following query retrieves the primary keys from all tables and views:
SELECT * FROM sys_primarykeys
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the key. |
SchemaName | String | The name of the schema containing the key. |
TableName | String | The name of the table containing the key. |
ColumnName | String | The name of the key column. |
KeySeq | String | The sequence number of the primary key. |
KeyName | String | The name of the primary key. |
sys_indexes¶
Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.
The following query retrieves all indexes that are not primary keys:
SELECT * FROM sys_indexes WHERE IsPrimary='false'
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the index. |
SchemaName | String | The name of the schema containing the index. |
TableName | String | The name of the table containing the index. |
IndexName | String | The index name. |
ColumnName | String | The name of the column associated with the index. |
IsUnique | Boolean | True if the index is unique. False otherwise. |
IsPrimary | Boolean | True if the index is a primary key. False otherwise. |
Type | Int16 | An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3). |
SortOrder | String | The sort order: A for ascending or D for descending. |
OrdinalPosition | Int16 | The sequence number of the column in the index. |
sys_connection_props¶
Returns information on the available connection properties and those set in the connection string.
When querying this table, the config connection string should be used:
jdbc:cdata:paypal:config:
This connection string enables you to query this table without a valid connection.
The following query retrieves all connection properties that have been set in the connection string or set through a default value:
SELECT * FROM sys_connection_props WHERE Value <> ''
Columns¶
Name | Type | Description |
---|---|---|
Name | String | The name of the connection property. |
ShortDescription | String | A brief description. |
Type | String | The data type of the connection property. |
Default | String | The default value if one is not explicitly set. |
Values | String | A comma-separated list of possible values. A validation error is thrown if another value is specified. |
Value | String | The value you set or a preconfigured default. |
Required | Boolean | Whether the property is required to connect. |
Category | String | The category of the connection property. |
IsSessionProperty | String | Whether the property is a session property, used to save information about the current connection. |
Sensitivity | String | The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms. |
PropertyName | String | A camel-cased truncated form of the connection property name. |
Ordinal | Int32 | The index of the parameter. |
CatOrdinal | Int32 | The index of the parameter category. |
Hierarchy | String | Shows dependent properties associated that need to be set alongside this one. |
Visible | Boolean | Informs whether the property is visible in the connection UI. |
ETC | String | Various miscellaneous information about the property. |
sys_sqlinfo¶
Describes the SELECT query processing that the connector can offload to the data source.
Discovering the Data Source's SELECT Capabilities¶
Below is an example data set of SQL capabilities. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.
Name | Description | Possible Values |
---|---|---|
AGGREGATE_FUNCTIONS | Supported aggregation functions. | AVG , COUNT , MAX , MIN , SUM , DISTINCT |
COUNT | Whether COUNT function is supported. | YES , NO |
IDENTIFIER_QUOTE_OPEN_CHAR | The opening character used to escape an identifier. | [ |
IDENTIFIER_QUOTE_CLOSE_CHAR | The closing character used to escape an identifier. | ] |
SUPPORTED_OPERATORS | A list of supported SQL operators. | = , > , < , >= , <= , <> , != , LIKE , NOT LIKE , IN , NOT IN , IS NULL , IS NOT NULL , AND , OR |
GROUP_BY | Whether GROUP BY is supported, and, if so, the degree of support. | NO , NO_RELATION , EQUALS_SELECT , SQL_GB_COLLATE |
STRING_FUNCTIONS | Supported string functions. | LENGTH , CHAR , LOCATE , REPLACE , SUBSTRING , RTRIM , LTRIM , RIGHT , LEFT , UCASE , SPACE , SOUNDEX , LCASE , CONCAT , ASCII , REPEAT , OCTET , BIT , POSITION , INSERT , TRIM , UPPER , REGEXP , LOWER , DIFFERENCE , CHARACTER , SUBSTR , STR , REVERSE , PLAN , UUIDTOSTR , TRANSLATE , TRAILING , TO , STUFF , STRTOUUID , STRING , SPLIT , SORTKEY , SIMILAR , REPLICATE , PATINDEX , LPAD , LEN , LEADING , KEY , INSTR , INSERTSTR , HTML , GRAPHICAL , CONVERT , COLLATION , CHARINDEX , BYTE |
NUMERIC_FUNCTIONS | Supported numeric functions. | ABS , ACOS , ASIN , ATAN , ATAN2 , CEILING , COS , COT , EXP , FLOOR , LOG , MOD , SIGN , SIN , SQRT , TAN , PI , RAND , DEGREES , LOG10 , POWER , RADIANS , ROUND , TRUNCATE |
TIMEDATE_FUNCTIONS | Supported date/time functions. | NOW , CURDATE , DAYOFMONTH , DAYOFWEEK , DAYOFYEAR , MONTH , QUARTER , WEEK , YEAR , CURTIME , HOUR , MINUTE , SECOND , TIMESTAMPADD , TIMESTAMPDIFF , DAYNAME , MONTHNAME , CURRENT_DATE , CURRENT_TIME , CURRENT_TIMESTAMP , EXTRACT |
REPLICATION_SKIP_TABLES | Indicates tables skipped during replication. | |
REPLICATION_TIMECHECK_COLUMNS | A string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication. | |
IDENTIFIER_PATTERN | String value indicating what string is valid for an identifier. | |
SUPPORT_TRANSACTION | Indicates if the provider supports transactions such as commit and rollback. | YES , NO |
DIALECT | Indicates the SQL dialect to use. | |
KEY_PROPERTIES | Indicates the properties which identify the uniform database. | |
SUPPORTS_MULTIPLE_SCHEMAS | Indicates if multiple schemas may exist for the provider. | YES , NO |
SUPPORTS_MULTIPLE_CATALOGS | Indicates if multiple catalogs may exist for the provider. | YES , NO |
DATASYNCVERSION | The Data Sync version needed to access this driver. | Standard , Starter , Professional , Enterprise |
DATASYNCCATEGORY | The Data Sync category of this driver. | Source , Destination , Cloud Destination |
SUPPORTSENHANCEDSQL | Whether enhanced SQL functionality beyond what is offered by the API is supported. | TRUE , FALSE |
SUPPORTS_BATCH_OPERATIONS | Whether batch operations are supported. | YES , NO |
SQL_CAP | All supported SQL capabilities for this driver. | SELECT , INSERT , DELETE , UPDATE , TRANSACTIONS , ORDERBY , OAUTH , ASSIGNEDID , LIMIT , LIKE , BULKINSERT , COUNT , BULKDELETE , BULKUPDATE , GROUPBY , HAVING , AGGS , OFFSET , REPLICATE , COUNTDISTINCT , JOINS , DROP , CREATE , DISTINCT , INNERJOINS , SUBQUERIES , ALTER , MULTIPLESCHEMAS , GROUPBYNORELATION , OUTERJOINS , UNIONALL , UNION , UPSERT , GETDELETED , CROSSJOINS , GROUPBYCOLLATE , MULTIPLECATS , FULLOUTERJOIN , MERGE , JSONEXTRACT , BULKUPSERT , SUM , SUBQUERIESFULL , MIN , MAX , JOINSFULL , XMLEXTRACT , AVG , MULTISTATEMENTS , FOREIGNKEYS , CASE , LEFTJOINS , COMMAJOINS , WITH , LITERALS , RENAME , NESTEDTABLES , EXECUTE , BATCH , BASIC , INDEX |
PREFERRED_CACHE_OPTIONS | A string value specifies the preferred cacheOptions. | |
ENABLE_EF_ADVANCED_QUERY | Indicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side. | YES , NO |
PSEUDO_COLUMNS | A string array indicating the available pseudo columns. | |
MERGE_ALWAYS | If the value is true, The Merge Mode is forcibly executed in Data Sync. | TRUE , FALSE |
REPLICATION_MIN_DATE_QUERY | A select query to return the replicate start datetime. | |
REPLICATION_MIN_FUNCTION | Allows a provider to specify the formula name to use for executing a server side min. | |
REPLICATION_START_DATE | Allows a provider to specify a replicate startdate. | |
REPLICATION_MAX_DATE_QUERY | A select query to return the replicate end datetime. | |
REPLICATION_MAX_FUNCTION | Allows a provider to specify the formula name to use for executing a server side max. | |
IGNORE_INTERVALS_ON_INITIAL_REPLICATE | A list of tables which will skip dividing the replicate into chunks on the initial replicate. | |
CHECKCACHE_USE_PARENTID | Indicates whether the CheckCache statement should be done against the parent key column. | TRUE , FALSE |
CREATE_SCHEMA_PROCEDURES | Indicates stored procedures that can be used for generating schema files. |
The following query retrieves the operators that can be used in the WHERE clause:
SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'
Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the Data Model section for more information.
Columns¶
Name | Type | Description |
---|---|---|
NAME | String | A component of SQL syntax, or a capability that can be processed on the server. |
VALUE | String | Detail on the supported SQL or SQL syntax. |
sys_identity¶
Returns information about attempted modifications.
The following query retrieves the Ids of the modified rows in a batch operation:
SELECT * FROM sys_identity
Columns¶
Name | Type | Description |
---|---|---|
Id | String | The database-generated ID returned from a data modification operation. |
Batch | String | An identifier for the batch. 1 for a single operation. |
Operation | String | The result of the operation in the batch: INSERTED, UPDATED, or DELETED. |
Message | String | SUCCESS or an error message if the update in the batch failed. |
Advanced Configurations Properties¶
The advanced configurations properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure. Click the links for further details.
Property | Description |
---|---|
User | The PayPal user account used to authenticate. |
Password | The password used to authenticate the user. |
Signature | The signature required to use the SOAP API. |
UseSandbox | A Boolean value indicating if you are using a Sandbox account. |
Property | Description |
---|---|
InitiateOAuth | Set this property to initiate the process to obtain or refresh the OAuth access token when you connect. |
OAuthClientId | The client ID assigned when you register your application with an OAuth authorization server. |
OAuthClientSecret | The client secret assigned when you register your application with an OAuth authorization server. |
OAuthAccessToken | The access token for connecting using OAuth. |
OAuthSettingsLocation | The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH . Alternatively, you can hold this location in memory by specifying a value starting with 'memory://' . |
CallbackURL | The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings. |
OAuthVerifier | The verifier code returned from the OAuth authorization URL. |
OAuthRefreshToken | The OAuth refresh token for the corresponding OAuth access token. |
OAuthExpiresIn | The lifetime in seconds of the OAuth AccessToken. |
OAuthTokenTimestamp | The Unix epoch timestamp in milliseconds when the current Access Token was created. |
Property | Description |
---|---|
SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
Property | Description |
---|---|
Location | A path to the directory that contains the schema files defining tables, views, and stored procedures. |
BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC. |
Tables | This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC. |
Views | Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC. |
Schema | The type of schema to use. |
Property | Description |
---|---|
IncludeDetails | A Boolean value indicating if you will get extra details (for example, the ShippingAddress fields when querying the Invoices table). |
MaxRows | Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses. |
Other | These hidden properties are used only in specific use cases. |
PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
UserDefinedViews | A filepath pointing to the JSON configuration file containing your custom views. |
Authentication¶
This section provides a complete list of authentication properties you can configure.
Property | Description |
---|---|
User | The PayPal user account used to authenticate. |
Password | The password used to authenticate the user. |
Signature | The signature required to use the SOAP API. |
UseSandbox | A Boolean value indicating if you are using a Sandbox account. |
User¶
The PayPal user account used to authenticate.
Data Type¶
string
Default Value¶
""
Remarks¶
Together with Password, this field is used to authenticate against the PayPal server.
Password¶
The password used to authenticate the user.
Data Type¶
string
Default Value¶
""
Remarks¶
The User and Password
are together used to authenticate with the server.
Signature¶
The signature required to use the SOAP API.
Data Type¶
string
Default Value¶
""
Remarks¶
The signature required to use the SOAP API.
Along with Signature
, User and Password are required to access the Transaction and TransactionDetails tables.
To create an API signature, you need a PayPal business account. To create the signature, log in and click Profile
. Click My Selling Tools
and, in the Selling Online section, click Update
in the API Access section. On the resulting page, click Request API Credentials
.
UseSandbox¶
A Boolean value indicating if you are using a Sandbox account.
Data Type¶
bool
Default Value¶
false
Remarks¶
A Boolean value indicating if you are using a Sandbox account.
OAuth¶
This section provides a complete list of OAuth properties you can configure.
Property | Description |
---|---|
InitiateOAuth | Set this property to initiate the process to obtain or refresh the OAuth access token when you connect. |
OAuthClientId | The client ID assigned when you register your application with an OAuth authorization server. |
OAuthClientSecret | The client secret assigned when you register your application with an OAuth authorization server. |
OAuthAccessToken | The access token for connecting using OAuth. |
OAuthSettingsLocation | The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH . Alternatively, you can hold this location in memory by specifying a value starting with 'memory://' . |
CallbackURL | The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings. |
OAuthVerifier | The verifier code returned from the OAuth authorization URL. |
OAuthRefreshToken | The OAuth refresh token for the corresponding OAuth access token. |
OAuthExpiresIn | The lifetime in seconds of the OAuth AccessToken. |
OAuthTokenTimestamp | The Unix epoch timestamp in milliseconds when the current Access Token was created. |
InitiateOAuth¶
Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.
Possible Values¶
OFF
, GETANDREFRESH
, REFRESH
Data Type¶
string
Default Value¶
OFF
Remarks¶
The following options are available:
OFF
: Indicates that the OAuth flow will be handled entirely by the user. An OAuthAccessToken will be required to authenticate.GETANDREFRESH
: Indicates that the entire OAuth Flow will be handled by the connector. If no token currently exists, it will be obtained by prompting the user via the browser. If a token exists, it will be refreshed when applicable.REFRESH
: Indicates that the connector will only handle refreshing the OAuthAccessToken. The user will never be prompted by the connector to authenticate via the browser. The user must handle obtaining the OAuthAccessToken and OAuthRefreshToken initially.
OAuthClientId¶
The client ID assigned when you register your application with an OAuth authorization server.
Data Type¶
string
Default Value¶
""
Remarks¶
As part of registering an OAuth application, you will receive the OAuthClientId
value, sometimes also called a consumer key, and a client secret, the OAuthClientSecret.
OAuthClientSecret¶
The client secret assigned when you register your application with an OAuth authorization server.
Data Type¶
string
Default Value¶
""
Remarks¶
As part of registering an OAuth application, you will receive the OAuthClientId, also called a consumer key. You will also receive a client secret, also called a consumer secret. Set the client secret in the OAuthClientSecret
property.
OAuthAccessToken¶
The access token for connecting using OAuth.
Data Type¶
string
Default Value¶
""
Remarks¶
The OAuthAccessToken
property is used to connect using OAuth. The OAuthAccessToken
is retrieved from the OAuth server as part of the authentication process. It has a server-dependent timeout and can be reused between requests.
The access token is used in place of your user name and password. The access token protects your credentials by keeping them on the server.
OAuthSettingsLocation¶
The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH . Alternatively, you can hold this location in memory by specifying a value starting with 'memory://'
.
Data Type¶
string
Default Value¶
%APPDATA%\PayPal Data Provider\OAuthSettings.txt
Remarks¶
When InitiateOAuth is set to GETANDREFRESH
or REFRESH
, the connector saves OAuth values to avoid requiring the user to manually enter OAuth connection properties and to allow the credentials to be shared across connections or processes.
Instead of specifying a file path, you can use memory storage. Memory locations are specified by using a value starting with 'memory://'
followed by a unique identifier for that set of credentials (for example, memory://user1). The identifier can be anything you choose but should be unique to the user. Unlike file-based storage, where credentials persist across connections, memory storage loads the credentials into static memory, and the credentials are shared between connections using the same identifier for the life of the process. To persist credentials outside the current process, you must manually store the credentials prior to closing the connection. This enables you to set them in the connection when the process is started again. You can retrieve OAuth property values with a query to the sys_connection_props
system table. If there are multiple connections using the same credentials, the properties are read from the previously closed connection.
The default location is "%APPDATA%\PayPal Data Provider\OAuthSettings.txt" with %APPDATA%
set to the user's configuration directory. The default values are
- Windows: "
register://%DSN
" - Unix: "%AppData%..."
- Mac: "%AppData%..."
where DSN is the name of the current DSN used in the open connection.
The following table lists the value of %APPDATA%
by OS:
Platform | %APPDATA% |
---|---|
Windows | The value of the APPDATA environment variable |
Mac | ~/Library/Application Support |
Linux | ~/.config |
CallbackURL¶
The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.
Data Type¶
string
Default Value¶
""
Remarks¶
During the authentication process, the OAuth authorization server redirects the user to this URL. This value must match the callback URL you specify in your app settings.
OAuthVerifier¶
The verifier code returned from the OAuth authorization URL.
Data Type¶
string
Default Value¶
""
Remarks¶
The verifier code returned from the OAuth authorization URL. This can be used on systems where a browser cannot be launched such as headless systems.
Authentication on Headless Machines¶
See to obtain the OAuthVerifier
value.
Set OAuthSettingsLocation along with OAuthVerifier
. When you connect, the connector exchanges the OAuthVerifier
for the OAuth authentication tokens and saves them, encrypted, to the specified location. Set InitiateOAuth to GETANDREFRESH to automate the exchange.
Once the OAuth settings file has been generated, you can remove OAuthVerifier
from the connection properties and connect with OAuthSettingsLocation set.
To automatically refresh the OAuth token values, set OAuthSettingsLocation and additionally set InitiateOAuth to REFRESH.
OAuthRefreshToken¶
The OAuth refresh token for the corresponding OAuth access token.
Data Type¶
string
Default Value¶
""
Remarks¶
The OAuthRefreshToken
property is used to refresh the OAuthAccessToken when using OAuth authentication.
OAuthExpiresIn¶
The lifetime in seconds of the OAuth AccessToken.
Data Type¶
string
Default Value¶
""
Remarks¶
Pair with OAuthTokenTimestamp to determine when the AccessToken will expire.
OAuthTokenTimestamp¶
The Unix epoch timestamp in milliseconds when the current Access Token was created.
Data Type¶
string
Default Value¶
""
Remarks¶
Pair with OAuthExpiresIn to determine when the AccessToken will expire.
SSL¶
This section provides a complete list of SSL properties you can configure.
Property | Description |
---|---|
SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
SSLServerCert¶
The certificate to be accepted from the server when connecting using TLS/SSL.
Data Type¶
string
Default Value¶
""
Remarks¶
If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.
This property can take the following forms:
Description | Example |
---|---|
A full PEM Certificate (example shortened for brevity) | -----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE----- |
A path to a local file containing the certificate | C:\\cert.cer |
The public key (example shortened for brevity) | -----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY----- |
The MD5 Thumbprint (hex values can also be either space or colon separated) | ecadbdda5a1529c58a1e9e09828d70e4 |
The SHA1 Thumbprint (hex values can also be either space or colon separated) | 34a929226ae0819f2ec14b4a3d904f801cbb150d |
If not specified, any certificate trusted by the machine is accepted.
Certificates are validated as trusted by the machine based on the System's trust store. The trust store used is the 'javax.net.ssl.trustStore' value specified for the system. If no value is specified for this property, Java's default trust store is used (for example, JAVA_HOME\lib\security\cacerts).
Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.
Schema¶
This section provides a complete list of schema properties you can configure.
Property | Description |
---|---|
Location | A path to the directory that contains the schema files defining tables, views, and stored procedures. |
BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC. |
Tables | This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC. |
Views | Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC. |
Schema | The type of schema to use. |
Location¶
A path to the directory that contains the schema files defining tables, views, and stored procedures.
Data Type¶
string
Default Value¶
%APPDATA%\PayPal Data Provider\Schema
Remarks¶
The path to a directory which contains the schema files for the connector (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location
property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.
Note
Given that this connector supports multiple schemas, the structure for PayPal custom schema files is as follows:
- Each schema is given a folder corresponding to that schema name.
- These schema folders are contained in a parent folder.
- The
parent folder
should be set as theLocation
, not an individual schema's folder.
If left unspecified, the default location is "%APPDATA%\PayPal Data Provider\Schema" with %APPDATA%
being set to the user's configuration directory:
Platform | %APPDATA% |
---|---|
Windows | The value of the APPDATA environment variable |
Mac | ~/Library/Application Support |
Linux | ~/.config |
BrowsableSchemas¶
This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.
Data Type¶
string
Default Value¶
""
Remarks¶
Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.
Tables¶
This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.
Data Type¶
string
Default Value¶
""
Remarks¶
Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the connector.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.
Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.
Views¶
Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.
Data Type¶
string
Default Value¶
""
Remarks¶
Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the connector.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.
Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.
Schema¶
The type of schema to use.
Possible Values¶
REST
, SOAP
Data Type¶
string
Default Value¶
SOAP
Remarks¶
The schemas available are REST (to use the REST API) and SOAP (to use the Classic API).
Miscellaneous¶
This section provides a complete list of miscellaneous properties you can configure.
Property | Description |
---|---|
IncludeDetails | A Boolean value indicating if you will get extra details (for example, the ShippingAddress fields when querying the Invoices table). |
MaxRows | Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses. |
Other | These hidden properties are used only in specific use cases. |
PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
UserDefinedViews | A filepath pointing to the JSON configuration file containing your custom views. |
IncludeDetails¶
A Boolean value indicating if you will get extra details (for example, the ShippingAddress fields when querying the Invoices table).
Data Type¶
bool
Default Value¶
false
Remarks¶
A Boolean value indicating if you will get extra details (for example, the ShippingAddress fields when querying the Invoices table).
MaxRows¶
Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Data Type¶
int
Default Value¶
-1
Remarks¶
Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Other¶
These hidden properties are used only in specific use cases.
Data Type¶
string
Default Value¶
""
Remarks¶
The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.
Specify multiple properties in a semicolon-separated list.
Integration and Formatting¶
Property | Description |
---|---|
DefaultColumnSize | Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000. |
ConvertDateTimeToGMT | Determines whether to convert date-time values to GMT, instead of the local time of the machine. |
RecordToFile=filename | Records the underlying socket data transfer to the specified file. |
PseudoColumns¶
This property indicates whether or not to include pseudo columns as columns to the table.
Data Type¶
string
Default Value¶
""
Remarks¶
This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".
Timeout¶
The value in seconds until the timeout error is thrown, canceling the operation.
Data Type¶
int
Default Value¶
60
Remarks¶
If Timeout
= 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.
If Timeout
expires and the operation is not yet complete, the connector throws an exception.
UserDefinedViews¶
A filepath pointing to the JSON configuration file containing your custom views.
Data Type¶
string
Default Value¶
""
Remarks¶
User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json
. The connector automatically detects the views specified in this file.
You can also have multiple view definitions and control them using the UserDefinedViews
connection property. When you use this property, only the specified views are seen by the connector.
This User Defined View configuration file is formatted as follows:
- Each root element defines the name of a view.
- Each root element contains a child element, called
query
, which contains the custom SQL query for the view.
For example:
{
"MyView": {
"query": "SELECT * FROM Payments WHERE MyColumn = 'value'"
},
"MyView2": {
"query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
}
}
Use the UserDefinedViews
connection property to specify the location of your JSON configuration file. For example:
"UserDefinedViews", C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json
Note that the specified path is not embedded in quotation marks.