Troubles to call the get_objkey function

Hi Romsar

It will depend on what you are trying to do.
a select statement like this will give you the data you need as you are running in SQL developer - I’m not sure without checking whether you need the _DB or field or not.

select COMPANY, IDENTITY, OBJKEY,
    ifsapp.Identity_Invoice_Info_API.Get_Objkey(COMPANY, IDENTITY, PARTY_TYPE) as OBJKEY_API,
    ifsapp.Identity_Invoice_Info_API.Get_Objkey(COMPANY, IDENTITY, PARTY_TYPE_DB) as OBJKEY_API2

from IDENTITY_INVOICE_INFO

where PARTY_TYPE_DB='CUSTOMER';

if you are trying to pass in the company, identity and objkey from an event action or a procedure, try replacing the details in the brackets with (COMPANY_,IDENTITY_,PARTY_TYPE)

I’m guessing you are testing the API call logic - as the API call will return the same value as the OBJKEY in your top select section - the api is referring to the same view as you are querying... 

Hello ​@WyrDavidB 

Initially, I made a SELECT statement on CUST_ORDER_INV_HEAD_UIV_ALL, so from this view, I wanted to get a custom field value (persistant type) but it was a fail, so I wrote my query like this :

select COMPANY, IDENTITY, SERIES_ID, INVOICE_NO, 

  ifsapp.Identity_Invoice_Info_cfp.Get_Cf$_Sfd_Cf_Sd_050(ifsapp.Identity_Invoice_Info_api.Get_Objkey(company_=>COMPANY, identity_=>IDENTITY, party_type_=>'Customer')) as ECLATEMENT

from CUST_ORDER_INV_HEAD_UIV_ALL

where PARTY_TYPE='CUSTOMER'

and objstate!='Cancelled'

;

but I realised that 

ifsapp.Identity_Invoice_Info_api.Get_Objkey(company_=>COMPANY, identity_=>IDENTITY, party_type_=>'Customer')

was already returning a null value.

So to make better tests, I was then calling the function on IDENTITY_INVOICE_INFO but it was still a fail, and to get the custom field value, I prefered to get it via a function instead of making a join with IDENTITY_INVOICE_INFO_CFV

​@romsar - there is something a bit odd happening here

I would expect this SQL to return a value in the QBJKEY_API column

You would normally pass in the company, identity and party_type in the way I am in the screen shots.
But there is no data being returned.

for sanity, I’ve just checked 2 other API calls in the same package - and these are returning data
 

I’ve seen something similar but I can’t recall the solution. Hopefully someone else will be able to assist

​@WyrDavidB 

I was making additional tests, I found something interresting but I have no real explanations...

So I made a test on a specific customer in which the custom field field can return a non null value (in my case, the value must either be NO or YES), by running this SELECT statement, I could get the value :

select COMPANY, IDENTITY, OBJKEY, PARTY_TYPE, 

    ifsapp.Identity_Invoice_Info_cfp.Get_Objkey(company_=>COMPANY, identity_=>IDENTITY, party_type_=>PARTY_TYPE) as OBJKEY_API,

    ifsapp.Identity_Invoice_Info_cfp.Get_Cf$_Sfd_Cf_Sd_050(ifsapp.Identity_Invoice_Info_cfp.Get_Objkey(company_=>COMPANY, identity_=>IDENTITY, party_type_=>PARTY_TYPE)) as ECLATEMENT

from IDENTITY_INVOICE_INFO

where PARTY_TYPE_DB='CUSTOMER'

and IDENTITY='CUS00127'

;

So to fix my issue, I replaced the call of the Identity_Invoice_Info_api package by Identity_Invoice_Info_cfp

and about the 3rd parameter, I put ‘Customer’

I don’t fully understand why the 3rd parameter must be ‘Customer’ but not ‘CUSTOMER’ as ‘CUSTOMER’ is a value in the IDENTITY_INVOICE_INFO_TAB table 

I made the test on an IFS cloud version, I can’t use the Identity_Invoice_Info_cfp package, I can only use the Identity_Invoice_Info_api one but still, Get_Objkey returns no value but get_def_currency…. very weird.

I’m making an IFS case

​@romsar Go look at the definition to see where the function is selecting from to answer your question. Just looking at the parameters doesn’t tell you everything about the data that needs to be passed. If it’s selecting from the table then it’s party_type is your party_type_db in the view. If you can’t look at the package you can get the same information from all_source. Other doubtful possibility is the RLS on the view

I got a feedback from the support and honestly, I’m surprised by their response :

Identity Invoice Info keeps a small memory cache so that repeated reads of the same customer do not query the table every time. Get_Def_Currency uses that cache, which is why currency is returned.

Get_Objkey uses the same cache, then returns the cache field for the record key. That field is never loaded. The method has found the customer — the currency proves that — and the key it hands back is empty.

The key on the database is the column ROWKEY. Screens and views show it as OBJKEY. Custom field values are stored against that key. Reading it from the view is the supported way to obtain it for this logical unit.

I don’t understand why Identity_Invoice_Info_API.get_objkey exists if it can only return a null value

this is what the standard package has...im not sure what the cfp would have

FUNCTION Get_Objkey (

   company_ IN VARCHAR2,

   identity_ IN VARCHAR2,

   party_type_ IN VARCHAR2 ) RETURN VARCHAR2

IS

   party_type_db_ identity_invoice_info_tab.party_type%TYPE := Party_Type_API.Encode(party_type_);

BEGIN

   IF (company_ IS NULL OR identity_ IS NULL OR party_type_db_ IS NULL) THEN

      RETURN NULL;

   END IF;

   Update_Cache___(company_, identity_, party_type_);

   RETURN micro_cache_value_.rowkey;

END Get_Objkey;

but looking at your query again, you already have objkey in your view IDENTITY_INVOICE_INFO. This is the same value as the rowkey from the table. You shouldn’t need to fetch it with the API. Not sure if you needed to select from IDENTITY_INVOICE_INFO or CUST_ORDER_INV_HEAD_UIV_ALL (kinda lost in the thread). But the original question of this post already has access to the objkey.

You can test that with this query that should return no rows
SELECT i.objkey, t.rowkey

FROM Identity_Invoice_Info i

LEFT JOIN Identity_Invoice_Info_tab t ON i.objkey = t.rowkey

WHERE t.rowkey IS NULL
 

The definition in the CFP version :

FUNCTION Get_Objkey (

   company_ IN VARCHAR2,

   identity_ IN VARCHAR2,

   party_type_ IN VARCHAR2 ) RETURN VARCHAR2

IS

   temp_ Public_Rec;

   CURSOR get_objkey IS

      SELECT objkey

      FROM IDENTITY_INVOICE_INFO

      WHERE company = company_

      AND identity = identity_

      AND party_type = party_type_;

BEGIN

   OPEN  get_objkey;

   FETCH get_objkey INTO temp_.objkey;

   CLOSE get_objkey;

   RETURN(temp_.objkey);

END Get_Objkey;

So that explains why the function is working on the CFP version.

in my SQL statement, I simply used the cfv view, that’s not the way I wanted to initially proceed, but at least, that works like a charm

​@PhillBronson 

I don’t understand your select statement, Identity_Invoice_Info is a view of Identity_Invoice_Info_tab, and your query returns no line in my side

​@romsar good! My example was showing that the view and the table have the value. And that you don’t need to use an API to get the objkey of the view you are already selecting from.

​@PhillBronson initially, I had a select statement on the CUST_ORDER_INV_HEAD_UIV_ALL view and I needed to know a custom field value from Identity_Invoice_Info, that’s why I wanted to use an api but my tests were a fail