JDBC Driver for SAP Concur

Build 24.0.9060

Reports

Create, update, delete, and query all reports owned by the user.

Table Specific Information

Select

The driver uses the SAP Concur APIs to process almost all searches to this table -- you can use server-side processing to search on any pseudocolumn and almost any combination of the remaining columns. The driver processes any other filters client-side within the driver. 

SELECT * FROM Reports WHERE Id = 'reportId'
SELECT * FROM Reports WHERE ApprovalStatusCode = 'code' AND PaymentType_p = 'PaymentTypeID' AND CreateDateBefore_p = '2018-01-01 10:00:00'

Insert

To insert a report the following column is required: Name. 

INSERT INTO Reports (Name, Custom1Value) VALUES ('Name', 'Test');

Update

To update a report the following column is required: Id. 

UPDATE Reports SET  Name = 'Name' WHERE Id = '12334'

Delete

Deletes are not supported for this table.

Columns

Name Type ReadOnly Description
ID [KEY] String True

The unique identifier of the resource.

ApproverLoginID String True

The Login ID of the report owner's expense approver.

Custom10ListItemID String True

For list fields, this is the list item ID.

Custom11ListItemID String True

For list fields, this is the list item ID.

Custom12ListItemID String True

For list fields, this is the list item ID.

Custom13ListItemID String True

For list fields, this is the list item ID.

Custom14ListItemID String True

For list fields, this is the list item ID.

Custom15ListItemID String True

For list fields, this is the list item ID.

Custom16ListItemID String True

For list fields, this is the list item ID.

Custom17ListItemID String True

For list fields, this is the list item ID.

Custom18ListItemID String True

For list fields, this is the list item ID.

Custom19ListItemID String True

For list fields, this is the list item ID.

Custom1ListItemID String True

For list fields, this is the list item ID.

Custom20ListItemID String True

For list fields, this is the list item ID.

Custom2ListItemID String True

For list fields, this is the list item ID.

Custom3ListItemID String True

For list fields, this is the list item ID.

Custom4ListItemID String True

For list fields, this is the list item ID.

Custom5ListItemID String True

For list fields, this is the list item ID.

Custom6ListItemID String True

For list fields, this is the list item ID.

Custom7ListItemID String True

For list fields, this is the list item ID.

Custom8ListItemID String True

For list fields, this is the list item ID.

Custom9ListItemID String True

For list fields, this is the list item ID.

OrgUnit1ListItemID String True

For list fields, this is the list item ID.

OrgUnit2ListItemID String True

For list fields, this is the list item ID.

OrgUnit3ListItemID String True

For list fields, this is the list item ID.

OrgUnit4ListItemID String True

For list fields, this is the list item ID.

OrgUnit5ListItemID String True

For list fields, this is the list item ID.

OrgUnit6ListItemID String True

For list fields, this is the list item ID.

PaidDate Datetime True

The date when all journal entries in the report was integrated with or extracted to the financial system.

PolicyID String False

The unique identifier of the policy that applies to this report. Maximum 64 characters.

AmountDueCompanyCard Decimal True

The total amount due to the company card for the report. Maximum 23 characters.

AmountDueEmployee Decimal True

The total amount due to the employee for the report. Maximum 23 characters.

ApprovalStatusCode String True

The approval status code for the report.

ApprovalStatusName String True

The report's approval status, in the OAuth consumer's language.

ApproverName String True

The name of the report owner's expense approver.

Country String True

The report country. Maximum 2 characters. Format: The ISO 3166-1 alpha-2 country code. Example: United States is US.

CountrySubdivision String True

The report country subdivision. Format: ISO 3166-2:2007 country subdivision.

CreateDate Datetime True

The date the report was created.

CurrencyCode String True

The ISO 4217 currency code for the expense report currency. Examples: USD - US dollars; BRL - Brazilian real; CAD - Canadian dollar; CHF - Swiss franc; EUR - Euro; GBO - Pound sterling; HKD - Hong Kong dollar; INR - Indian rupee; MXN - Mexican peso; NOK - Norwegian krone; SEK - Swedish krona.

Custom10Code String True

For list fields, this is the list item code.

Custom10Type String True

The custom field type. Possible values: Amount, Boolean, ConnectedList, Date, Integer, List, Number, Text

Custom10Value String False

The value in the Org Unit or Custom field. For list fields, this is the name of the list item. Maximum length: 48 characters

Custom11Code String True

For list fields, this is the list item code.

Custom11Type String True

The custom field type. Possible values: Amount, Boolean, ConnectedList, Date, Integer, List, Number, Text

Custom11Value String False

The value in the Org Unit or Custom field. For list fields, this is the name of the list item. Maximum length: 48 characters

Custom12Code String True

For list fields, this is the list item code.

Custom12Type String True

The custom field type. Possible values: Amount, Boolean, ConnectedList, Date, Integer, List, Number, Text

Custom12Value String False

The value in the Org Unit or Custom field. For list fields, this is the name of the list item. Maximum length: 48 characters

Custom13Code String True

For list fields, this is the list item code.

Custom13Type String True

The custom field type. Possible values: Amount, Boolean, ConnectedList, Date, Integer, List, Number, Text

Custom13Value String False

The value in the Org Unit or Custom field. For list fields, this is the name of the list item. Maximum length: 48 characters

Custom14Code String True

For list fields, this is the list item code.

Custom14Type String True

The custom field type. Possible values: Amount, Boolean, ConnectedList, Date, Integer, List, Number, Text

Custom14Value String False

The value in the Org Unit or Custom field. For list fields, this is the name of the list item. Maximum length: 48 characters

Custom15Code String True

For list fields, this is the list item code.

Custom15Type String True

The custom field type. Possible values: Amount, Boolean, ConnectedList, Date, Integer, List, Number, Text

Custom15Value String False

The value in the Org Unit or Custom field. For list fields, this is the name of the list item. Maximum length: 48 characters

Custom16Code String True

For list fields, this is the list item code.

Custom16Type String True

The custom field type. Possible values: Amount, Boolean, ConnectedList, Date, Integer, List, Number, Text

Custom16Value String False

The value in the Org Unit or Custom field. For list fields, this is the name of the list item. Maximum length: 48 characters

Custom17Code String True

For list fields, this is the list item code.

Custom17Type String True

The custom field type. Possible values: Amount, Boolean, ConnectedList, Date, Integer, List, Number, Text

Custom17Value String False

The value in the Org Unit or Custom field. For list fields, this is the name of the list item. Maximum length: 48 characters

Custom18Code String True

For list fields, this is the list item code.

Custom18Type String True

The custom field type. Possible values: Amount, Boolean, ConnectedList, Date, Integer, List, Number, Text

Custom18Value String False

The value in the Org Unit or Custom field. For list fields, this is the name of the list item. Maximum length: 48 characters

Custom19Code String True

For list fields, this is the list item code.

Custom19Type String True

The custom field type. Possible values: Amount, Boolean, ConnectedList, Date, Integer, List, Number, Text

Custom19Value String False

The value in the Org Unit or Custom field. For list fields, this is the name of the list item. Maximum length: 48 characters

Custom1Code String True

For list fields, this is the list item code.

Custom1Type String True

The custom field type. Possible values: Amount, Boolean, ConnectedList, Date, Integer, List, Number, Text

Custom1Value String False

The value in the Org Unit or Custom field. For list fields, this is the name of the list item. Maximum length: 48 characters

Custom20Code String True

For list fields, this is the list item code.

Custom20Type String True

The custom field type. Possible values: Amount, Boolean, ConnectedList, Date, Integer, List, Number, Text

Custom20Value String False

The value in the Org Unit or Custom field. For list fields, this is the name of the list item. Maximum length: 48 characters

Custom2Code String True

For list fields, this is the list item code.

Custom2Type String True

The custom field type. Possible values: Amount, Boolean, ConnectedList, Date, Integer, List, Number, Text

Custom2Value String False

The value in the Org Unit or Custom field. For list fields, this is the name of the list item. Maximum length: 48 characters

Custom3Code String True

For list fields, this is the list item code.

Custom3Type String True

The custom field type. Possible values: Amount, Boolean, ConnectedList, Date, Integer, List, Number, Text

Custom3Value String False

The value in the Org Unit or Custom field. For list fields, this is the name of the list item. Maximum length: 48 characters

Custom4Code String True

For list fields, this is the list item code.

Custom4Type String True

The custom field type. Possible values: Amount, Boolean, ConnectedList, Date, Integer, List, Number, Text

Custom4Value String False

The value in the Org Unit or Custom field. For list fields, this is the name of the list item. Maximum length: 48 characters

Custom5Code String True

For list fields, this is the list item code.

Custom5Type String True

The custom field type. Possible values: Amount, Boolean, ConnectedList, Date, Integer, List, Number, Text

Custom5Value String False

The value in the Org Unit or Custom field. For list fields, this is the name of the list item. Maximum length: 48 characters

Custom6Code String True

For list fields, this is the list item code.

Custom6Type String True

The custom field type. Possible values: Amount, Boolean, ConnectedList, Date, Integer, List, Number, Text

Custom6Value String False

The value in the Org Unit or Custom field. For list fields, this is the name of the list item. Maximum length: 48 characters

Custom7Code String True

For list fields, this is the list item code.

Custom7Type String True

The custom field type. Possible values: Amount, Boolean, ConnectedList, Date, Integer, List, Number, Text

Custom7Value String False

The value in the Org Unit or Custom field. For list fields, this is the name of the list item. Maximum length: 48 characters

Custom8Code String True

For list fields, this is the list item code.

Custom8Type String True

The custom field type. Possible values: Amount, Boolean, ConnectedList, Date, Integer, List, Number, Text

Custom8Value String False

The value in the Org Unit or Custom field. For list fields, this is the name of the list item. Maximum length: 48 characters

Custom9Code String True

For list fields, this is the list item code.

Custom9Type String True

The custom field type. Possible values: Amount, Boolean, ConnectedList, Date, Integer, List, Number, Text

Custom9Value String False

The value in the Org Unit or Custom field. For list fields, this is the name of the list item. Maximum length: 48 characters

EverSentBack Bool True

Whether the report has ever been sent back to the employee. Format: Y/N

HasException Bool True

Whether the report has exceptions. Format: Y/N

LastComment String True

The text of the most recent comment on the report.

LastModifiedDate Datetime True

The date the report header was last modified.

LedgerName String True

The name of the expense report ledger. Maximum 20 characters.

Name String False

The name of the report.

OrgUnit1Code String True

For list fields, this is the list item code.

OrgUnit1Type String True

The custom field type. Possible values: Amount, Boolean, ConnectedList, Date, Integer, List, Number, Text

OrgUnit1Value String False

The value in the Org Unit or Custom field. For list fields, this is the name of the list item. Maximum length: 48 characters

OrgUnit2Code String True

For list fields, this is the list item code.

OrgUnit2Type String True

The custom field type. Possible values: Amount, Boolean, ConnectedList, Date, Integer, List, Number, Text

OrgUnit2Value String False

The value in the Org Unit or Custom field. For list fields, this is the name of the list item. Maximum length: 48 characters

OrgUnit3Code String True

For list fields, this is the list item code.

OrgUnit3Type String True

The custom field type. Possible values: Amount, Boolean, ConnectedList, Date, Integer, List, Number, Text

OrgUnit3Value String False

The value in the Org Unit or Custom field. For list fields, this is the name of the list item. Maximum length: 48 characters

OrgUnit4Code String True

For list fields, this is the list item code.

OrgUnit4Type String True

The custom field type. Possible values: Amount, Boolean, ConnectedList, Date, Integer, List, Number, Text

OrgUnit4Value String False

The value in the Org Unit or Custom field. For list fields, this is the name of the list item. Maximum length: 48 characters

OrgUnit5Code String True

For list fields, this is the list item code.

OrgUnit5Type String True

The custom field type. Possible values: Amount, Boolean, ConnectedList, Date, Integer, List, Number, Text

OrgUnit5Value String False

The value in the Org Unit or Custom field. For list fields, this is the name of the list item. Maximum length: 48 characters

OrgUnit6Code String True

For list fields, this is the list item code.

OrgUnit6Type String True

The custom field type. Possible values: Amount, Boolean, ConnectedList, Date, Integer, List, Number, Text

OrgUnit6Value String False

The value in the Org Unit or Custom field. For list fields, this is the name of the list item. Maximum length: 48 characters

OwnerName String True

The name of the expense report owner.

PaymentStatusCode String True

The code for the payment status of the report.

PaymentStatusName String True

The report's payment status, in the OAuth consumer's language.

PersonalAmount Decimal True

The total amount of expenses marked as personal. Maximum 23 characters.

ProcessingPaymentDate Datetime True

The date that the report completed all approvals and was ready to be extracted for payment.

ReceiptsReceived Bool True

If Y, then this report has its receipt receipt confirmed by the Expense Processor. Format: Y/N

SubmitDate Datetime True

The date the report was submitted.

Total Decimal True

The total amount of the report.

TotalApprovedAmount Decimal True

The total amount of approved expenses in the report. Maximum 23 characters.

TotalClaimedAmount Decimal True

The total amount of all non-personal expenses in the report. Maximum 23 characters.

URI String True

null

UserDefinedDate Datetime False

The date of the report assigned by the user.

WorkflowActionUrl String True

The URL to post a workflow action to the report using the Post Report Workflow Action function.

HasImages Boolean False

Determines if the report has at least one expense entry with an entry image or if there is a report image for this report. FORMAT: true or false.

ExpenseTypeCode String False

The expense type code that is the expense type for at least one expense entry in the report. Use ExpenseTypeCode from Response of GET Expense Group Configurations V3.

LoginId String False

Optional. The login ID of the report owner(s) to use when searching for reports. If the value is set to LoginID, reports for the report owner with this login ID value are returned. If the value is set to ALL, reports for all report owners are returned. If this parameter is not specified, reports for the OAuth Consumer are returned. The access token owner (OAuth Consumer) must have the Web Services Admin role to use this parameter.

PaymentType String False

The unique identifier for the payment type that is the payment type for at least one expense entry in the report. Use PaymentTypeID from Response of GET Expense Group Configurations V3 to obtain valid payment types.

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
PaidDateBefore_p String

The report paid date is before this date.Format: YYYY-MM-DD

CreateDateBefore_p String

The report create date is before this date.Format: YYYY-MM-DD

ProcessingPaymentDateBefore_p String

The report processing payment date is before this date.Format: YYYY-MM-DD

EntryTransactionDateBefore_p String

The entry transaction date for at least one expense entry in the report is before this date.Format: YYYY-MM-DD

SubmitDateAfter_p String

The report submit date is after this date.Format: YYYY-MM-DD

UserDefinedDateBefore_p String

The report user defined date is before this date.Format: YYYY-MM-DD

BatchID_p String

The unique identifier for a payment batch where there is at least one report payee within the report. Use the BatchID from Response of GET Payment Batch List.

ProcessingPaymentDateAfter_p String

The report processing payment date is after this date. Format: YYYY-MM-DD

ModifiedDateBefore_p String

The report modified date is before this date.Format: YYYY-MM-DD

UserDefinedDateAfter_p String

The report user defined date is after this date.Format: YYYY-MM-DD

HasAttendees_p String

Determines if the report has at least one expense entry with an attendee. FORMAT: true or false.

PaidDateAfter_p String

The report paid date is after this date.Format: YYYY-MM-DD

ExpenseGroupConfigID_p String

The unique identifier for the expense group configuration associated to the report's expense group. Use the ID from the Response of the Expense Group Configurations V3.

IsTestUser_p String

The report owner is a test user using the report for testing purposes in a non-production envirnment. FORMAT: true or false.

HasBillableExpenses_p String

The IsBillable flag for at least one expense entry in the report. FORMAT: true or false.

HasVAT_p String

Determines if the report has at least one expense entry with VAT details. FORMAT: true or false.

ReimbursementMethod_p String

The method the report owner will be reimbursed. VALUES: ADPPAYR - ADP Payroll; APCHECK - AP (Company Check); CNQRPAY - Expense Pay; PMTSERV - Other Payment Service. NOTE: PAY_PAL is NOT supported.

AttendeeTypeCode_p String

The report contains expense entries that have attendees of the specified type.

ModifiedDateAfter_p String

The report modified date is after this date.Format: YYYY-MM-DD

CreateDateAfter_p String

The report create date is after this date.Format: YYYY-MM-DD

SubmitDateBefore_p String

The report submit date is before this date.Format: YYYY-MM-DD

CountryCode_p String

The report country. Maximum 2 characters. Format: The ISO 3166-1 alpha-2 country code. Example: United States is US.

EntryTransactionDateAfter_p String

The entry transaction date for at least one expense entry in the report is after this date.Format: YYYY-MM-DD

VendorName_p String

The Vendor Description that is the vendor for at least one expense entry in the report.

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