This content has been marked as final. Show 28 replies
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!
v_api_error VARCHAR (500);
(p_assignment_id => '7736',
p_information_type => 'XXSAC_AUTO_ENROLMENT',
p_aei_information_category => 'XXSAC_AUTO_ENROLMENT',
p_assignment_extra_info_id => v_extra,
p_object_version_number => v_object,
p_aei_information1 => '1',
p_aei_information2 => '18-MAR-2013',
p_aei_information3 => 'Test' );
v_api_error := SQLERRM;
v_errcode := SQLCODE;
No error message?
What happens if you put that date parameter value in as '2013/03/18 00:00:00' ?
Can you amend it to put the following immediately after the api call:
dbms_output.put_line('Returned id: '||TO_CHAR(v_extra));
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.
Not sure what that tells me though :-)
When you say 'I'm not getting anything in my DBMS_OUTPUT window', do you mean literally nothing, or are you seeing something like 'Returned id: ' but with no number after it?
Nothing at all - just an empty screen
Aha! Are you running this process through TOAD or some other sql environment, or as a concurrent request via the application?
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.
Running it through TOAD.
When I run that query there are no rows returned.
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:
Edited by: clive_t on 18-Mar-2013 13:17
Yes I have the DBMS output window opened and it has a green button at the top left but it's showing an empty screen
What about if you have that other dbms_output statement in your exception 'WHEN OTHERS' clause?
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.
It now seems to be working consistently - I've honestly no idea why it's suddenly working but it is!