If you create a basic component interface on JOB_DATA allowing Application Designer to default all of the properties, Application Designer generates a unique constraint error the first time you try to save the new component interface.
Reproducing the Bug
File > New … Component Interface
Pick a Component: JOB_DATA
Do you want to default the properties on the underlying Component definition: JOB_DATA? Yes
File > Save … Enter any name
Error Message
File: e:\pt849-903-R1-retail\peopletools\SRC\psmgr\mgrgensql.cppSQL error. Stmt #: 1741 Error Position: 0 Return: 805 - ORA-00001: unique constraint (SYSADM.PS_PSBCITEM) violated
Failed SQL stmt:INSERT INTO PSBCITEM ( BCNAME, BCTYPE, BCITEMPARENT, BCITEMNAME, SEQUENCE_NBR_6, BCACCESS, BCSCROLL, BCSCROLLNUM, BCSCROLLNAME, RECNAME, SUBRECNAME, FIELDNAME, COMMENTSHORT, MPDEVICEPERSISTINT, SYNCID) VALUES ( :1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15)
Workaround
Just save it a second time.
File > Save … Enter any name
I don’t know why but it just worked the second time.
October 17, 2007 at 6:02 am
What tools release are you on?
October 17, 2007 at 4:35 pm
Graham,
I believe the release is PeopleTools 8.46 with HCM 8.9. I think I also noticed the problem on PeopleTools 8.49 with HCM 9.0, but I can’t remember exactly. I am in the middle of an install right now, but I will boot the HR instances as soon as I can and try to determine if it was both or just one of them.
October 23, 2007 at 9:28 pm
I just tried this in the PeopleTools 8,49, HRMS 9.0 version that I have access to, and the error occurred just the same. Hope that helps.
May 1, 2008 at 11:00 pm
Jobcode is getting updated to the future dated row when inserting Functional Partition row that is prior to future dated row, but greater than the history rows. Example:JC3004 – 6/1/2008
JC2004 – 3/2/2008. insert row has 3004 jobcode instead of JC2004
May 1, 2008 at 11:02 pm
this is issue is for job data ci
October 21, 2009 at 5:21 am
Any specific reason why the unique constraint error occurs for the very first time while saving the JOB DATA CI? Please share the info
October 21, 2009 at 6:35 am
I never did figure it out. It has been a while since I have tried to create a CI for Job. To me, it is a PeopleTools bug, and I was hoping that they would have it fixed. I just tried it in PeopleTools 8.49, and it still happens.
I ran a trace, so here are some more details:
http://psst0101.wordpress.com/2009/10/21/458/
October 21, 2009 at 6:35 am
[...] Bugs, Component Interface, PeopleTools — digitaleagle @ 6:34 am This is another look at an old post. In summary, the problem is that when you create a Component Interface for the Job Data [...]
October 21, 2009 at 8:46 pm
Out of curiosity, I dropped the index on PSBCITEM and then saved the component interface as TEST_JOB_DATA_CI. I then ran the following SQL to check if there were any duplicated rows:
select BCNAME, BCTYPE, BCITEMPARENT, BCITEMNAME, count(*)
from PSBCITEM
where BCNAME = ‘TEST_JOB_DATA_CI’
group by BCNAME, BCTYPE, BCITEMPARENT, BCITEMNAME
having count(*) > 1;
Result was:
BCNAME BCTYPE BCITEMPARENT BCITEMNAME COUNT(*)
TEST_JOB_DATA_CI 4 JOB TERMINATION_DT_1 2
So in my case it looks like the field TERMINATION_DT_1 is duplicating in the PSBCITEM table:
BCITEMNAME SEQUENCE_NBR_6 BCSCROLL RECNAME FIELDNAME SYNCID
TERMINATION_DT_1 403 1 DERIVED_HR_CORE TERMINATION_DT 3752
TERMINATION_DT_1 257 1 JOB TERMINATION_DT 3886
That is, both items have the TERMINATION_DT_1 field occurs twice on the key field BCITEMNAME.
This is also the case when you look at the CI properties in the JOB scroll for the TERMINATION_DT_1 field on the records DERIVED_HR_CORE and JOB.
So, I removed the CI I had created (by exporting to file with delete flag and reimporting), recreated the unique index on PSBCITEM, and performed the “save twice” trick to get it to save the CI.
I queried the PSBCITEM table again, and the TERMINATION_DT_1 returned was the one for the RECNAME, DERIVED_HR_CORE. So for some reason, during the second save, app designer decides to drop the TERMINATION_DT_1 field from the JOB scroll.
So I figured I could do this manually, by deleting the CI again (using a file project), recreating the CI, and then going into the properties and removing the field TERMINATION_DT_1 from the JOB record of the JOB scroll (or renaming the field to TERMINATION_DT_1_1). Both these options worked for me.
Can anyone else confirm if this is the case for them? Thanks.