Hi

I have created a package to load grades information as per code:
http://pastebin.com/m57c2146f

Data in my temporary table(currently loading 1st record only for testing!):
http://pastebin.com/m681542e3

The data in my table in sql :
Name Null? Type
----------------------------------------- -------- -----
SEQUENCE NUMBER
NAME VARCHAR2(70)
HEALTHCARE_SERVICE VARCHAR2(80)
DEATH_SERVICE VARCHAR2(70)
LONG_DISABILITY VARCHAR2(70)
MOBILE_PHONE VARCHAR2(5)
HOLIDAY_ENTITLEMENT NUMBER(5)
STD_PROBATION NUMBER(5)
STD_NOTICE NUMBER(5)
REPORTED_AS VARCHAR2(30)
PAYCODE VARCHAR2(5)
INCENTIVE_SCHEME VARCHAR2(20)
FLEX_BENEFIT VARCHAR2(20)

Also: I have removed all the value sets for the descriptive flexfield under:
Application > flexfield > Descriptive > segments

and i have successfully inserted the record manually by cutting and pasting field values from my data file!

So in SQL*PLUS: Why does it still complain of this:

Problem:
I am getting error:
LINE/COL ERROR
-----------------------------------------------------------------
71/1 PLS-00306: wrong number or types of arguments in call to
'CREATE_GRADE'

I went to line 71 within the file in notepad and i didnt find anything i am confused.

Thanks in advance

Tovia singer

Tovia

I would reckon it's objecting to your omission of the p_grade_id output parameter from the parameter list.

HI CT

Thanks so much

I added p_grade_id:=v_grade_id;
and it compiled successfull but didnt create any records, so i checked the commit, and it was in the place same place as in my jobs api call.
Also checked the application and there where no grades so it shouldnt have any problem loading them.

My data file is:
http://pastebin.com/m2bce6d4a

There should be an error if it doesnt load the data i dont understand!

Thanks in advance

Tovia Singer

So what gets reported when you run it? Presumably you get a load of lines in your 'dbms_output' destination that show

'Ex:<grade_id>,<object_version_number>'

What are the actual numbers being returned, if any?

Hi CT

Basically i set serverouput on:
set serveroutput on size 20000;

But i still get the following output:

SQL> @XXcompany_INT_GRADES_LOAD

Package created.

No errors.

Package body created.

No errors.

The revised code with p_grade_id:
http://pastebin.com/m5fc0c461

Thanks in advance

Tovia

OK so it compiles, but what happens when you run it?

HI CT

Sorry:

SQL> execute XXcompany_PKG_GRADES.XXcompany_LOAD_GRADES;
Ex:,
Ex:,
Ex:,
Ex:,
Ex:,
Ex:,
Ex:,
Ex:,
Ex:,
Ex:,
Ex:,
Ex:,
Ex:,
Ex:,
Ex:,
Ex:,
Ex:,
Ex:,
Ex:,
Ex:,
Ex:,
Ex:,
Ex:,
Ex:,
Ex:,
Ex:,
Ex:,
Ex:,

PL/SQL procedure successfully completed.

And it doesnt actually load any data,

Thanks in advance

Tovia S

Could it be that the only reason you're not seeing exceptions is because you are trying to report them via the FND_FILE package, which really only works if you're running this from the conc. manager. As a temporary measure, can you change the FND_FILE references to DBMS_OUTPUT.put_line and re-run it? Also, I would say in the latest version of your script, you might benefit from moving the line of code currently at line 128:

DBMS_OUTPUT.PUT_LINE('Ex:'||v_grade_id||','||v_object_version_number);

to immediately *after* the api call, e.g. at line 119 (to quote the numbers from your Pastebin listing)

Hi CT

Sorry for the delay in feedback,

I did as you said above:

Latest code:
http://pastebin.com/m2c1dff1d

The feedback from/ error in sql:

SQL> execute XXcompany_PKG_GRADES.XXcompany_LOAD_GRA
Ex:,
-20001ORA-20001: FLEX-ID DOES NOT EXIST:
Ex:,
Ex:,
-20001ORA-20001: FLEX-ID DOES NOT EXIST:
Ex:,
Ex:,
-20001ORA-20001: FLEX-ID DOES NOT EXIST:
Ex:,
Ex:,
-20001ORA-20001: FLEX-ID DOES NOT EXIST:
Ex:,
Ex:,
-20001ORA-20001: FLEX-ID DOES NOT EXIST:
Ex:,
Ex:,
-20001ORA-20001: FLEX-ID DOES NOT EXIST:
Ex:,
Ex:,
-20001ORA-20001: FLEX-ID DOES NOT EXIST:
Ex:,
Ex:,
-20001ORA-20001: FLEX-ID DOES NOT EXIST:
Ex:,
Ex:,
-20001ORA-20001: FLEX-ID DOES NOT EXIST:
Ex:,
Ex:,
-20001ORA-20001: FLEX-ID DOES NOT EXIST:
Ex:,
Ex:,
-20001ORA-20001: FLEX-ID DOES NOT EXIST:
Ex:,
Ex:,
-20001ORA-20001: FLEX-ID DOES NOT EXIST:
Ex:,
Ex:,
-20001ORA-20001: FLEX-ID DOES NOT EXIST:
Ex:,
Ex:,
-20001ORA-20001: FLEX-ID DOES NOT EXIST:
Ex:,
Ex:,
-20001ORA-20001: FLEX-ID DOES NOT EXIST:
Ex:,
Ex:,
-20001ORA-20001: FLEX-ID DOES NOT EXIST:
Ex:,
Ex:,
-20001ORA-20001: FLEX-ID DOES NOT EXIST:
Ex:,
Ex:,
-20001ORA-20001: FLEX-ID DOES NOT EXIST:
Ex:,
Ex:,
-20001ORA-20001: FLEX-ID DOES NOT EXIST:
Ex:,
Ex:,
-20001ORA-20001: FLEX-ID DOES NOT EXIST:
Ex:,
Ex:,
-20001ORA-20001: FLEX-ID DOES NOT EXIST:
Ex:,
Ex:,
-20001ORA-20001: FLEX-ID DOES NOT EXIST:
Ex:,
Ex:,
-20001ORA-20001: FLEX-ID DOES NOT EXIST:
Ex:,
Ex:,
-20001ORA-20001: FLEX-ID DOES NOT EXIST:
Ex:,
Ex:,
-20001ORA-20001: FLEX-ID DOES NOT EXIST:
Ex:,
Ex:,
-20001ORA-20001: FLEX-ID DOES NOT EXIST:
Ex:,
Ex:,
-20001ORA-20001: FLEX-ID DOES NOT EXIST:
Ex:,
Ex:,
-20001ORA-20001: FLEX-ID DOES NOT EXIST:
Ex:,
PL/SQL procedure successfully completed.
SQL>

Thanks in advance

Tovia Singer

Ah, this is of course a problem you will have seen before, in your 'job loading' process (see separate thread)

Recall that the text associated with this message code is "ID &ID for the flexfield segment &SEGMENT does not exist in the value set &VALUESET." In other words, you still have a valueset in place somewhere that is not liking the data you're trying to send to a desc flex segment.

Hmm, thinking about it, it's more likely to be the ID flex segments that are giving this issue. What value sets if any are on the actual grade segments?

Hi CT

Thanks a million!!!!!

It was due to the fact that i had the character - in my grade band column, and basically when loaded into oracle via sql*loader it showed up as a dark square, which is odd, because i was storing - in a varchar2 column which should accept most characters.

So band value set in segment1 column of grade flexfield disagreed for that reason.

Now carrying on working through the br100, i have agreed with the client i will manually setup:

Grade Rates, Pay Scale, Point Scale and Grade Scale

Instead of using API's, as theres not allot of data there apart from grade rates!

Glad they agreed, but what follows the above is positions which are created against jobs, and theres way too many to do manually, so i will have to write api code for them.

Hopefully i can start my Positions code later on today or tommorow once the bove grade related info has been created, i will leave the value sets out for the grade desc flexfield where it causes problems with the data file.

I wanted to attach my code for your reference however theres no place here so i stuck it in pastebin which lasts a month i think:
http://pastebin.com/maad5d71

Thanks again!

Kind Regards

Tovia Singer

Tovia

It's a bit quirky, but I understand that if you edit your post, it should give you the option of attaching files. However, tread carefully as you may be infringing the client's copyright. Unless any specific provisions have been made, you can usually take it that whatever code you write on behalf of a client, the IPR is automatically vested in the client. Someone please correct me if I am wrong here.

One caveat with the grade DFF value sets being removed(?) from the grade structure definition: doing that will of course allow you to load any old data into those DFF segments - however, if you are intending to reintroduce those value sets after the loading, you may well run into difficulties either at the point of changing the DFF segment to point to a value set, or at the point when you try and change something about the grade. Problem is, it will always try and validate the entire record prior to actually changing whatever data item being changed, at which point you may well see failures occurring on fields that haven't been changed.

As far as position loading is concerned, there's no real issue with it apart from you need to get hold of the appropriate job id and organisation id as a minimum in order that those values may be passed to the API.

Hi CT

I cant work out why i cant save grade rate:
http://www.hrmsaces.co.uk/ubbthreads7/ubbthreads.php?ubb=showflat&Number=3424#Post3424

Have looked in lookutypes and checked all grades exist under:
workstructures > grades > description

thanks in advance


Tovia Singer

Sorry, I haven't been able to reproduce this problem in my R12 instance.

Hi CT

I just remmember looking back through my documentation, i didnt well i couldnt find the conc process on 11i (11i version 11.5.10):

Run Concurrent Request: Security List Maintenance (Obsolete)

Now what i understand is from reading that i have to run this process after all teh flexfields have been created and before ntering data for jobs,positions,grades, to make them available etc.

Has the concurent process name chnaged? do you know anything about security list mainatence?

thanks in advance

Tovia Singer

There's a new one without the word obsolete in it!

Hi CT

I need to run the security list maintenance proces, however concurrent manager is down on development, and i dont have access to dba for this instance untill call request goes through (edenbrook), we run on win 2k, client is updating in october to unix hpux, however the concurrent manager is up as a process, but when i run any request i get:

Phase - inactive
status - No Manager

Alternative is to migrate all my setup that i have done so far onto another isntance (will take me a while, i have allot of repsonsibilities, and setup data.

I cleared out:
fnd_concurrent_processes as per article:
http://www.tek-tips.com/viewthread.cfm?qid=62090&page=15

In sql*Plus:
user: APPLSYS
delete from fnd_concurrent_processes;
pdate fnd_concurrent_queues set running_processes=0;

Is there a diagnostic script i can run to find out why it didnt start?

I mean the service is started but as soon as i run a request i get the above messages highlighted in yellow.

I logged in as sysadmin:
concurrent > Manager > Administer >
Internal Manager - Actual = 1, Target =1, Pending = 0
Conflict Resolution Manager - Actual = 0, Target =1, Pending = 19
Scheduler/Prereleaser Manager - Actual = 0, Target =1, Pending = null (no value)
Session History Cleanup - Actual = 0, Target =1, Pending = 0
Standard Manager - ctual = 0, Target = 3, Pending = 99

And so on.. Many are inactive but i think the above are the only ones i need, is there a workflow process i need to run?

I mean theres no activate button fo rthe above managers i have listed.

Thanks in advance

Tovia Singer



Theres quiet a few

Hi Geoff

Regards my problem with creating grade rates:
Basically its really bizarre another finding:

I can assign the grades i created to jobs via the jobs description form, grades tab,

But when i navigate to grade rates, and try to create a grade rate before proceeding to the detail section i get: ORA-01403: no data found ORA-0

Does thi mean i still need to run the security list mainatenance process, eventhough it seems grades are availble to assign top jobs, ?

Just for your info, it seems create grade rates form is a payroll form i found out:

help > about oracle applications:

Login
----------------------------------------
Site : UNKNOWN
Application : Human Resources
Responsibility : XXX UK HRMS Manager
Security Group : Standard
User Name : SINGER

----------------------------------------
Database Server
----------------------------------------
RDBMS : 9.2.0.5.0
Oracle Applications : 11.5.10
Machine : MPC
User : APPS
Oracle SID : DEV

----------------------------------------
Current Form
----------------------------------------
Form Application : Payroll
Form Name : PAYWSGEV



Thanks in advance

Tovia Singer

I don't see the relevance of the Sec List Maint process to be honest.

Go to the Grade Rates Form - to be certain, have you created a Grade Rate with the Unit 'Money'?

Hi Geoff

Sorry should have clarified:

1.
I created my grades:
Workstructures > Grade > Description

2.
I proceeded to create grade rates:
Workstructures > Grade > Grade Rate

Name : XXcompany_section1 Units:Money
There is a desc flexfield i think next to Units field but its greyed out etc.

Then i save the header record and i get:
ORA-01403: no data found ORA-0

Thanks in advance

Tovia Singer

Hi Geoff

Ok there is something i read about menus and responsibility settings, and the reason i get the ora-01403: no data found Ora-0

Just to confirm, unless you can think of any other pre-requist i could have missed:

Basically the custom responsibility i created to setup all oracle hrms has the following info:

Application: Human Resources
Effective Date: 07-JUN-2008

Data Group
----------
Name: Standard
Application: Human Resources

Menu: UK HRMS Manager

Request Group
-------------
Name: UK HRMS Reports & Processes
Application: Human Resources


Is this correct?

Thanks In advance

Tovia Singer

Tovia - can't recreate that - you need to raise it with Oracle I am afraid. The menu/responsibility look fine.

Is Grade Flex field is defined correctly?

Is 'Allow Dynamic inserts' is checked while defining the structure for the Grade flexfield.

Is flexfield definition is compiled successfully?

Also check the 'Additional Pay Rate Details' Descriptive flex definition.

Hi Krishna

Ok i enabled the "additional pay rate flexfield", and compiled it succesfully.

I then went to grade rate screen and when i entered Name and Units (money) i get the following error:

ORA-01403: no data found
ORA-0

So clicked on details button of the rror message and i got this:

ORA-01403: no data found
ORA-0
--------------------------------------------------------------
FRM-40735: ON-INSERT trigger r


Really bizare error?

Thanks in Advance

Tovia

Hi CT

Just read something about changing oracle tables, and it said if you chnage oracle 11i tables(sometime syou have to etc..) then th no data found error can occur as i am getting when i attempt to create grade rates, i basically remmeber i deleted all records from:
FND_CONCURRENT_PROCESSES
and
FND_CONCURRENT_REQUESTS

because concurrent manager was down, could this have caused the problem?

Thanks

Also i set trace on in the grade rate screen, how do i find teh trace file? it doesnt give me location.

Thanks in advance

Tovia Singer

Tovia

Whilst I would say that deleting data from tables in a non-controlled fashion is never a good idea unless absolutely necessary (and even then only on the instructions of Oracle Support), I doubt that the tables you cite are the reason for the problem.

As far as trace files are concerned, they are usually directed to the directory path asigned to the database parameter USER_DUMP_DEST

There is a way in SQL that you can see where these paths are pointing at for a given database. In SQL, do this:

SELECT name, value FROM v$parameter
WHERE name = 'user_dump_dest';

The path shown in the value column is the path on the server where your trace file will be found. Of course it is a .trc file, which tends to be a bit difficult to read. You can perform a 'tkprof' command on it to churn out some more readable output from that. Forget the syntax for that now, but if at the Unix prompt you do:

TKPROF ?

You'll get a 'usage' text which should help some.

Hi Tovia,

There is an outstanding bug in the system you might need to apply the Patch# 6781672

FYI
------------------------------------
Extract from Metalink Note:556249.1
-------------------------------------
Cause
------
When "ICX: Numeric Character" sets with 99.999,99, the Grade Rate Form fields (Minimum, Maximum
and Mid Value) gives the following error:

ORA-01403: no data found
FRM-40735: WHEN-VALIDATE-ITEM trigger raised unhandled exception ORA-06502.

This issue has been fixed in the file PAY_TOP/forms/US/PAYWSGEV.fmb 115.39
You have file PAY_TOP/forms/US/PAYWSGEV.fmb and version 115.38 which is lower than this fixed
version.

This is explained in the following bug:
Bug 6346419 - PAYWSGEV: CREATING GRADE RATE GIVES ERROR FRM-40735

Solution
---------
Fix for this bug will be provided in the next 11i RUP patch, expected for summer 2008.

A one-off for patch level FP K RUP1 is available in Patch 6781672

Also check is hrglobal.drv is applied correctly?

Thanks Krishna i have raised a tar with oracle see what happens.

Thanks so much for everyone and your help as well,

appreciated

Tovia Singer

Thanks CT i have decided to leave this one for oracle support, i have raised a tar.

Also i am working on positions now, so i have positions then taskflows left and i have completed br100 setup, for 11.5.10

thanks again

Tovia