Search code examples
sqloracleuser-interfacedatabase-designoracle-apex

Need Help In Enhancing Auto-Fill Functionality in Oracle APEX for Multi-Level Approval Systems

I'm in the process of implementing a multi-level approval system in Oracle APEX, following a guide similar to this one: reference-blog. Currently, when a user logs in, only the manager name, employee name, and employee code are auto-filled. However, I need to expand this functionality to include additional fields such as designation and division.

I've attempted several approaches, including creating a new table with the necessary parameters, modifying the Action SQL Query in task definitions:

select e.empno, e.emp_name, e.operation, e.department, e.email_id, e.designation, e.division, e.address, m.emp_name as mgr_name 
  from emp_2 e, emp_2 m 
 where m.empno(+)=e.mgr
   and e.empno=:APEX$TASK_PK

, and adding parameters in task definitions, modifying the SQL query in participants in task definition:

select  emp_name, operation, department, email_id, designation, division, address from emp_2 where empno=(select mgr from emp_2 where empno=(select empno from emp_1 where emp_name=:APP_USER))

. Additionally, I've modified the SQL query on the "Apply for Travel" page:

select e.empno, e.emp_name, e.operation, e.department, e.email_id, e.designation, e.division, e.address, m.emp_name as mgr_name 
  from emp_2 e, emp_2 m 
 where m.empno(+)=e.mgr
   and e.empno=:APEX$TASK_PK

. Despite these efforts, I encounter an error when attempting to open the page.

Could someone guide me on the correct procedure to extend the auto-fill functionality in Oracle APEX? Any insights or suggestions would be greatly appreciated. Thank you!

error-description

This is how the screen looks without errors, I want to add more fields:

og-pic

Here's what I get while debugging:

debug-msg

Solution

  • To find out what is raising the "NO DATA FOUND" error that is shown on the error page, run the page in debug mode and check the debug logs. That will show you what is the sql statement that is raising the error. Without that information it's very hard to debug your issue.

    When you say "autofill", what page are you referring to ? Can you add a screenshot ? I don't think any of the queries you're changing should be changed for that functionality.

    --update 1 after question edit--

    From what I can see you only need to update the form query (section starting with "In the Page Designer, create a new Form and name it New Travel Request...") with the additional columns. The where clause in that section is and e.empno=:P3_EMPNO and there is no reference to the task primary key. At that point there is no task yet, so :APEX$TASK_PK will be NULL and that is causing the no data found. I would think that is the only change needed, no change needed to in action query or task parameters. Those additional fields are just attributes of a individual task record, they do not impact the functionality of the task workflow.