APEX

Hi there,
Running into a bit of a problem with getting a user_id with calling a procedure in a package.

Business Need
To be able to track who created and updated records in a table

Solution
Using the whois concept of oracle EBS
created_by, creation_date, last_updated_by, last_update_date

Problem
The API is using a function throughout the application that has been built in PL/SQL.
This was a standard I used on the forms side and was using my own user_table.
Now with using APEX there is already a user table so I want to replace this.
I thought it would be as easy as replacing the select.
Somehow though the value is not being returned.
I keep getting the following error from the application when running it through the browser interface:
ORA-01400: cannot insert NULL into ("XCM"."KDM_REQ_DEPARTMENT"."CREATED_BY")
The weird thing is that testing this locally I did not get any issues.

Using the following function to get the user_id:
function user_id
return number
is
action_name constant varchar2 (30) := 'USER_ID';
l_workspace_id number;
l_user_id number;
begin
--dbms_output.put_line (nvl (v ('APP_USER'), user)) ;
--l_user_id := htmldb_util.get_user_id (nvl (v ('APP_USER'), user)) ;
select workspace_id into l_workspace_id from
apex_workspace_apex_users
where user_name = nvl (v ('APP_USER'), user)
;
wwv_flow_api.set_security_group_id(l_workspace_id);
select
user_id
into
l_user_id
from
wwv_flow_users
where
user_name = nvl (v ('APP_USER'), user) ;
if (l_user_id is null) then
return - 1;
else
return l_user_id;
end if;
exception
when others then
return - 1;
end user_id;

--------
Apex 4.2
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
Linux version 2.6.32-279.19.1.el6.i686
---------

Edited by: Hilton on Feb 1, 2013 1:09 PM