access the virtual tables with input parameters in CAP java

[apoorvamanjunath]

Our Java application is currently running in the NEO environment, and it uses the Remote Query Client to create SQL queries to retrieve data from the IC and BW system’s calculation views. These views provide data based on the input parameter passed. Remote Query Client is not supported on CF.

For Cloud foundry We have the Virtual tables created in a schema pointing to the Calculation views of IC and BW system. I've completed the setup to access the schema from our CAP project and can successfully create synonyms.

The query formed with RQC to query the IC and BW looks something like this

SELECT
 * 
FROM "_SYS_BIC"."dmtp.reporting/CL_TP_ACCOUNTS_PLANNING"('PLACEHOLDER' = ('$$IP_PLNG_END_DATE$$',
   '20240930'),
   'PLACEHOLDER' = ('$$IP_PLANYEAR$$',
   'Z1_2021'),
   'PLACEHOLDER' = ('$$IP_CURRENCY$$',
   'EUR'),
   'PLACEHOLDER' = ('$$IP_TP_ID$$',
   '''CRM-TP00-CRM-1944'''),
   'PLACEHOLDER' = ('$$IP_PLNG_BEG_DATE$$',
   '20231001'),
   'PLACEHOLDER' = ('$$IP_CATEGORY$$',
   'LICENSE'))

I am trying to achieve the same with CAP methodology. I tried to follow the Tutorial but it is not working.

I created the entity as below

@cds.persistence.exists
Entity CL_TP_ACCOUNTS_PLANNING(IP_PLNG_END_DATE : String(100), IP_PLANYEAR : String(100), IP_CURRENCY : String(100), IP_TP_ID : String(100), IP_PLNG_BEG_DATE : String(100), IP_CATEGORY : String(100)) {
        PLANGUID: Binary(16)  @title: 'PLANGUID: PLANGUID' ; 
        EXTERNAL_ID: String(24)  @title: 'EXTERNAL_ID: EXTERNAL_ID' ; 
        ACCOUNTID: String(10)  @title: 'ACCOUNTID: ACCOUNTID' ; 
        ROOT_GUID: Binary(16)  @title: 'ROOT_GUID: ROOT_GUID' ; 
        TP_ACC_CATEGORY: String(2)  @title: 'TP_ACC_CATEGORY: TP_ACC_CATEGORY' ; 
        ACCOUNT_NAME: String(80)  @title: 'ACCOUNT_NAME: ACCOUNT_NAME' ; 
        PARTNER_GUID: Binary(16)  @title: 'PARTNER_GUID: PARTNER_GUID' ; 
        PARTNER_NAME: String(5000)  @title: 'PARTNER_NAME: PARTNER_NAME' ; 
        QUARTER_1_ONPREM: Decimal(34, 2)  @title: 'QUARTER_1_ONPREM: QUARTER_1_ONPREM' ; 
        TARGET_CURR: String(6)  @title: 'TARGET_CURR: TARGET_CURR' ; 
        QUARTER_2_ONPREM: Decimal(34, 2)  @title: 'QUARTER_2_ONPREM: QUARTER_2_ONPREM' ; 
        QUARTER_3_ONPREM: Decimal(34, 2)  @title: 'QUARTER_3_ONPREM: QUARTER_3_ONPREM' ; 
        QUARTER_4_ONPREM: Decimal(34, 2)  @title: 'QUARTER_4_ONPREM: QUARTER_4_ONPREM' ; 
        QUARTER_1_CLOUD: Decimal(34, 2)  @title: 'QUARTER_1_CLOUD: QUARTER_1_CLOUD' ; 
        QUARTER_2_CLOUD: Decimal(34, 2)  @title: 'QUARTER_2_CLOUD: QUARTER_2_CLOUD' ; 
        QUARTER_4_CLOUD: Decimal(34, 2)  @title: 'QUARTER_4_CLOUD: QUARTER_4_CLOUD' ; 
        QUARTER_3_CLOUD: Decimal(34, 2)  @title: 'QUARTER_3_CLOUD: QUARTER_3_CLOUD' ; 
        RECORD_GUID: Binary(16)  @title: 'RECORD_GUID: RECORD_GUID' ; 
        SALES_BAG_CLOUD: String(500)  @title: 'SALES_BAG_CLOUD: SALES_BAG_CLOUD' ; 
        SALES_BAG_ON_PREM: String(500)  @title: 'SALES_BAG_ON_PREM: SALES_BAG_ON_PREM' ; 
        TOTAL_CLOUD: Decimal(34)  @title: 'TOTAL_CLOUD: TOTAL_CLOUD' ; 
        TOTAL_ON_PREM: Decimal(34)  @title: 'TOTAL_ON_PREM: TOTAL_ON_PREM' ; 
        QUARTER_1_SERVICES: Decimal(34, 2)  @title: 'QUARTER_1_SERVICES: QUARTER_1_SERVICES' ; 
        QUARTER_2_SERVICES: Decimal(34, 2)  @title: 'QUARTER_2_SERVICES: QUARTER_2_SERVICES' ; 
        QUARTER_3_SERVICES: Decimal(34, 2)  @title: 'QUARTER_3_SERVICES: QUARTER_3_SERVICES' ; 
        QUARTER_4_SERVICES: Decimal(34, 2)  @title: 'QUARTER_4_SERVICES: QUARTER_4_SERVICES' ; 
        TOTAL_SERVICES: Decimal(34)  @title: 'TOTAL_SERVICES: TOTAL_SERVICES' ; 
        SALES_BAG_SERVICES: String(255)  @title: 'SALES_BAG_SERVICES: SALES_BAG_ON_PREM' ; 
}

The service definition looks like below

using {spa.ic as my} from '../db/tpmService-data-model';
service tpmService {  entity Foo as projection on my.CL_TP_ACCOUNTS_PLANNING;}

I expose the entity as service and when try to access the this service like below

***/***/Foo1(IP_PLNG_END_DATE ='20240930',IP_PLANYEAR='Z1_2021',IP_CURRENCY='EUR',IP_TP_ID='CRM-TP00-CRM-1944',IP_PLNG_BEG_DATE='20231001',IP_CATEGORY='LICENSE)

But i receive the following error

{
    "error": {
        "code": "400",
        "message": "There are 1 key properties instead of the expected 0."
    }
}

If i am using JDBC methodology to access the virtual tables directly I am able to receive the data. can anyone please suggest me on what i should do make this work with CAP methodology.