JDBC Driver for SAP Concur

Build 24.0.9062

FiscalYears

Retrieve a list of all fiscal years.

Table Specific Information

Select

The 本製品 uses the SAP Concur APIs to process filters that refer to Id, UserName, and ExternalId. The 本製品 processes other filters client-side within the 本製品.

For example, the 本製品 will offload the processing of the following queries to the SAP Concur APIs:

SELECT * FROM FiscalYears
SELECT * FROM FiscalYears WHERE IncludeRemoved = 'True'
SELECT * FROM FiscalYears WHERE LastModified > '2024-02-20 09:51:20.0'
SELECT * FROM FiscalPeriod WHERE fiscalYearId = '7af8977c-d996-4ca7-834a-a8e5b719fa95'

Insert

To create a fiscal Year the following columns are required: Name, StartDate, EndDate, Status and MonthlyFiscalPeriods MonthlyFiscalPeriods can either be an aggregate object containing the full list of fiscal periods or the name of a temp table.

Insert using a temp table:

INSERT INTO FiscalPeriod#TEMP (Name, PeriodType,StartDate,EndDate,Status) VALUES ('JAN', 'MONTHLY',  '2023-01-01', '2023-01-31', 'OPEN')
INSERT INTO FiscalPeriod#TEMP (Name, PeriodType,StartDate,EndDate,Status) VALUES ('FEB', 'MONTHLY',  '2023-02-01', '2023-02-28', 'OPEN')
INSERT INTO FiscalPeriod#TEMP (Name, PeriodType,StartDate,EndDate,Status) VALUES ('MAR', 'MONTHLY',  '2023-03-01', '2023-03-31', 'OPEN')

INSERT INTO FiscalYears (name, startDate, endDate,Status, MonthlyFiscalPeriods) VALUES ('NewPeriod', '2023-01-01', '2023-03-31', 'OPEN', 'FiscalPeriod#TEMP')

Insert using a Json aggregate:

INSERT INTO FiscalYears (name, startDate, endDate,Status, MonthlyFiscalPeriods) VALUES ('NewPeriod', '2023-01-01', '2023-03-31', 'OPEN', '[{"name": "JAN","periodType": "MONTHLY","startDate": "2023-01-01","endDate": "2023-01-31","fiscalPeriodStatus": "OPEN"}]')

Update

The API treats update operations as a replace. The flow is the same as the Insert operation with the difference that the Id of the fiscal year to be updated needs to be specified in the query

INSERT INTO FiscalPeriod#TEMP (Name, PeriodType,StartDate,EndDate,Status) VALUES ('JAN', 'MONTHLY',  '2023-01-01', '2023-01-31', 'OPEN')
INSERT INTO FiscalPeriod#TEMP (Name, PeriodType,StartDate,EndDate,Status) VALUES ('FEB', 'MONTHLY',  '2023-02-01', '2023-02-28', 'OPEN')
INSERT INTO FiscalPeriod#TEMP (Name, PeriodType,StartDate,EndDate,Status) VALUES ('MAR', 'MONTHLY',  '2023-03-01', '2023-03-31', 'OPEN')

UPDATE FiscalYears SET startDate='2023-01-01',endDate='2023-03-31',Status = 'OPEN', Name = 'TEST' , MonthlyFiscalPeriods = 'FiscalPeriod#TEMP' WHERE id  = 'c891b0fb-ae64-45dc-84e4-a43d2adf4332'

Delete

In order to delete a fiscal year the Id is required. You can either specify the fiscal year Id in the query or the provider will resolve the Id internally by processing the criteria.

When Id is not specified an extra request will be issued to find the Id of all fiscal years with a name = 'TEST'.

DELETE FROM fiscalYears WHERE name = 'TEST'

When Id is specified in the query the provider will issue a delete request directly with no extra processing:

DELETE FROM fiscalYears WHERE Id = 'c891b0fb-ae64-45dc-84e4-a43d2adf4332'

Columns

Name Type ReadOnly Description
Id [KEY] String False

The budget service's key for this object.

Name String False

The name of this fiscal year. Must be unique for this entity.

Status String False

The status of this fiscal year. Supported values: OPEN, CLOSED, REMOVED

StartDate Date False

The start date for this fiscal year. The distance between start date and end date may not be more than two years.

EndDate Date False

The end date for this fiscal year. The distance between start date and end date may not be more than two years.

CurrentYear Boolean False

True if this the current fiscal year based on the current date and time, False otherwise.

LastModified Datetime False

The UTC date and time when this object was last changed.

DisplayName String False

Display name for fiscal year. For date range budget item we use this field to display.

MonthlyFiscalPeriods String False

If true, the service will return all Fiscal Years, including those that were previously removed. If not supplied, this field defaults to false.

Pseudo-Columns

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

Name Type Description
IncludeRemoved Boolean

If true, the service will return all Fiscal Years, including those that were previously removed. If not supplied, this field defaults to false.

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