Baz hinted at this in another thread but I thought I would bring it out into a thread of its own. . .We have a situation where we desire to use Data Pump to load element entries. All fine and dandy except: these days one has the option to configure an input value to be validated via a Valueset. Not only that but said valueset may be of the table-based persuasion. This is actually very bad news for us here at the well-known paymasters of our nation's armed services. . .Because these wretched valuesets contain references to the FND_SESSIONS table and in a couple of instances also refer to $PROFILES$.PER_BUSINESS_GROUP_ID the Data Pump process fails as: .1. It can't set itself a row in FND_SESSIONS .2. It can't interpret the context of $PROFILES$ . .The standard 'workaround' for these issues would be to set something up in an API-level before/after user hook that sorts out the FND_SESSIONS row and the apps_initialise process. For us this is a potentially very tortuous process to set up (we'd have to do it for every API that deals with valuesets). Also the additional run-time burden is likely to be significant as it will run for each batch line processed. . .So after all that here's my question: .Can anyone tell me if they've been able to 'persuade' the DP engine (or even the slave process) to take on an fnd_sessions row and to do an apps_initialise? If so could I get a wee sneaky-beaky at what you added? . .Cheers mateys . .CT

CT . .Baz is your man - I think I'm right in saying he spent all his time at a certain national airline hacking Data Pump to bits and worked out how to register custom api's e.g. . .INSERT INTO hr_api_modules . (api_module_id . api_module_type . module_name . data_within_business_group . legislation_code . module_package . last_update_date . last_updated_by . last_update_login . created_by . creation_date) . VALUES . (hr_api_modules_s.nextval . 'AI' . 'CREATE_OSE' . 'Y' . null . 'XXCONHRP_OSE_API_PKG' . sysdate . -1 . -1 . -1 . sysdate) ./ . .execute hr_pump_meta_mapper.generate('xxconhrp_ose_api_pkg' 'create_ose'); . .So could you create your own dp api that is simply a wrapper that will do the insert in fnd_sessions call apps_initialise and then the standard api? . .Failing that I think the way dp works out how to validate stuff is controlled by rows in hr_pump_module_parameters. . .HTH . .Jay

OK we have shed a little light on what exactly is going on here (NB all investigations have been undertaken on an 11.5.10 instance)... . .It appears that where there is validation of data against a flexfield structure (eg UPDATE_EMP_ASG_CRITERIA) the api does the decent thing and calls HR_KFLEX_UTILITY.set_Session and HR_KFLEX_UTILITY.set_profile prior to calling the upd_or_sel_kflex_comb - so there is every reason to expect that any valuesets used by flexfields will be properly executed during the relevant api call. . .HOWEVER - In the validation of element entry values the valueset validation is only being done in the context of an input value with not a sniff of a flexfield structure anywhere. .So even though the validation routine PAY_INPUT_VALUES_PKG (called from HR_ENTRY_API) contains a piece of code that properly resolves any references to $PROFILES$ because it doesn't set a session date it cannot properly resolve table-based validations involving joins with FND_SESSIONS. . .Now comes the best bit - you'll know from bitter experience that the historical response in these situations was to 'use a user-hook'? Well guess which one api doesn't have a user-hook capability! . .Hurrah!

Word from support (when asked when we encountered the same issue) is that you should not be making reference to $PROFILE$ stuff in valuesets. . .However i do recall a rather wayward support representative hinting that we could simply 'disable' the valueset whilst loading and then re-enabling it afterwards PROVIDING we could guarantee that the data was sound as a pound.

We've done a quick test with using the direct api call (py_element_entry_api.create_element_entry) and lo and behold we get the same problem (HR_34927_ELE_ENTRY_VSET_INVLD). However when one quietly creates an FND_SESSIONS row beforehand all is well! Interestingly the valueset in question contained references to $PROFILES$ as well as FND_SESSIONS and I didn't need to do anything explicit to monkey around with $PROFILES$. Ergo it must be handling it correctly - in fact if you look at the package function PAY_INPUT_VALUES_PKG.decode_vset_meaning you'll see for yourself that it disects the 'where' clause looking for instances of $PROFILES$ to replace with kosher values! . .So we're all TAR'd up now watch this space...

You didn't like my suggested hack then? I was educated at the Copper School of Hack don't you know. . .Doesn't this problem exist in other api's though - I have vague memories of past conversions were the standard practice was turn off any table validation values (person ass ppg dff) - or am I just going mad with SOY EOY????????

[quote:b4e86e5e40]You didn't like my suggested hack then? I was educated at the Copper School of Hack don't you know. [/quote:b4e86e5e40] .This I know... :roll: . .I've not looked in intimate detail at each and every API that looks at flexfields but I did look at the UPDATE_EMP_ASG_CRITERIA wherein one finds a liberal sprinkling of references to HR_KFLEX_UTILITY.set_session .set_profile etc. for the various flexfields it deals with. . .What I'm finding now though is that there seems to be no equivalent in the descriptive flex package HR_DFLEX_UTILITY!!! Oh gawd....

You could always do what we had to do in the past before they (HR Dev) sorted the Flex validation...and that is comment out the dflex_utility code so that it does nothing (other than return). Provided you can validate the values before loading them (which could potentially be quicker and safer) it makes the loading job a lot easier.

I rather fancy that were I to suggest that at this particular site I'd be lined up to partake in the next bayonet practice (receiving rather than dishing out). But I'll bear it in mind - maybe I'll con someone else into making the suggestion...

Wouldn't give them much bayonet practice aiming at something as [s:4db0be105a]big[/s:4db0be105a] slim as that. . .<Post amended by moderator to replace wholly inaccurate adjective with something a lot nearer the truth>

Hi, I know this is an old one but couldn't find anything more recent.

I'm having the same problem trying input a value for a position key flex using the create position API. The segment has a validation type of table and returns an error when I try to pass a value via the API

Is there now a way round the table validation?

Hi Mr Corbon262, and welcome to the forum.

Can I ask, are you able to reproduce the details of the validation 'where' clause here? I take it that the values that you would load, are valid when entered via the form?

Could be you need a wrapper around the API, that first sets up the appropriate session info; then register that with DP.

Here's the where clause from the apps
"WHERE business_group_id = :$PROFILES$.per_business_group_id
AND internal_external_flag = 'INT'
AND (date_to is null OR date_to > sysdate)
ORDER BY name"

I'm using a wrapper with the following look up for organization_id

CURSOR Get_segment11_org IS
SELECT organization_id
FROM HR_ALL_ORGANIZATION_UNITS
WHERE business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND internal_external_flag = 'INT'
AND UPPER(name) = UPPER(l_org_name)
AND (date_to is null OR date_to > l_start_date)

but no joy

OK, just out of interest what's the error message that you're getting back from the API call?

I still think it's to do with the interpretation of that :$PROFILES$ thing... Just humour me for a minute, and try doing some sort of apps_initialise at the start of your wrapper script, e.g.

FND_GLOBAL.apps_initialise (user, responsibility etc);

Just one other thing: what's the rest of the definition of the valueset? Do you, for instance, have something in the 'ID column' such as ORGANIZATION_ID?

Cheers for the welcome

I added the FND_GLOBAL.APPS_INITIALIZE(-1, 53826, 800) and it all works a treat.

I can't thank you enough for this, I've been chasing my tail with this for days.

I guess you don't ask you don't get

Again thank you

No worries Mr Corbon262, delighted to have been able to help - I guess that's what forums like this are all about!