Another thing, you should always use TRUNC(SYSDATE) when comparing against any effective_start_date and effective_end_date columns, ie:
where trunc(sysdate) between per.effective_start_date and per.effective_end_date
and per.person_id = adr.person_id
and adr.date_to is null
and per.person_id = asg.person_id and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
and asg.position_id = pos.position_id
I'm still having issues with this (apologies for delay in replying, was off sick with kidney infection).
Anyway, I've stripped it back to the script below where I'm hard coding an assignment and not doing any sql statement or any loop.
I've manually inserted a row through the system for this assignment_id so I'm just replicating the values it put in when I did that.
However, it's STILL not inserting into the table.
I know this should be easy but no idea where I'm going wrong - it must be something stupid!
It's made no difference and I'm not getting anything in my DBMS_OUTPUT window.
Interestingly, if you go back to my original post, you'll see that I have a working version of this for information_type 'XXSAC_PAYROLL_NOTIFICATION_DET'.
If I amend that existing working API just to change the information_type to 'XXSAC_INITIAL_ASSESSMENT', it stops working and doesn't input anything.
However, if I amend the one I'm working on now to have an information_type of 'XXSAC_PAYROLL_NOTIFICATION_DET' instead, it still doesn't work.
Can you please check that the EIT in which you are trying to insert data whether its been registerd for having single record ? this is set as a property when you are registering the EIT it asks for whether Multiple rows (Y/N), you can check the value selected while registering in table PER_PEOPLE_INFO_TYPES
Query - select multiple_occurences_flag from PER_PEOPLE_INFO_TYPES where information_type = <your EIT name>
IF the output of the query is "N" then update it to "Y" and re-try.
OK, in TOAD, in the sql window where you have your code, you will hopefully see a tab in the bottom part of the screen (where you see the results of queries etc) labelled 'DBMS Output' - does it say '(disabled)'? If so, click on the tab and click the red dot on the left of that part of the screen, until it becomes green. Then run your process again.
PS You might also want to put this as the first line in your exception:
Just as a bizarre update.
I ran the script twice there and both times it inserted the row I was expecting,
I've just ran the very same script another 3 times and it's not inserted anything any of those 3 times.