Hi All,
I'm running into a few problems with my shuttle.
The idea is that employees will be added to a department. Employees can either already be linked to another department or not.
At (1) the department is chosen to which the employees will be added.
When this is chosen, the right side of the shuttle should display all employees already added to this department. (This works well when the left side is empty).
Page item 1 is called EMP_DEPARTMENTS and it's a popup LOV which retrieves department ID's.
At (2) and (3) the employee data on the left side can be filtered.
The page items are popup LOV's and are called STORE and DEPARTMENT. They retrieve store and department ID's.
Employees can be retrieved based on cost center (store) or department.
The desired situation is that when a department is chosen at (1) and a filter is chosen at (2) or (3), that the employee data appears on the left, and all employees from that list which already fall under the department chosen at (1), should be pre-loaded onto the right side.
The problem is that when a department is chosen in (1) and I then filter on store or department, ALL data is moved to the left, instead of having the employees under EMP_DEPARTMENTS on the right side and employees under STORE or DEPARTMENT on the left side.
The queries for the shuttle (4) (EMPLOYEES_SHUTTLE) are something like:
LIST OF VALUES (SQL QUERY)
select employee_name d
, employee_id r
from table
where ((store_id = :STORE and department_id is null) or
(store_id is null and department_id = :DEPARTMENT) or
(store_id = :STORE and department_id = :DEPARTMENT))
OR department_id = :EMP_DEPARTMENT -- I actually do not want this condition, but the shuttle does not retrieve values if this isn't included
Cascading LOV parent items: STORE, DEPARTMENT, EMP_DEPARTMENT
Items to submit: EMP_DEPARTMENT, EMP_SHUTTLE
SOURCE (SQL QUERY RETURNING COLON SEPARATED LIST)
select employee_name
from table
where department_id = :EMP_DEPARTMENT
Used: Always, replacing any existing value in session state
Javascript function and global variable declaration on the page:
var htmldb_delete_message='"DELETE_CONFIRM_MSG"';
function sortSelectOptions(pThis){
var t=$($x(pThis));
var o=t.children("option");
o.detach();
o.sort(function(a,b){
a=a.firstChild.nodeValue;
b=b.firstChild.nodeValue;
if(a==b){
return 0;
}
return (a>b)?1:-1;
});
t.append(o);
}
Processing
On change EMP_DEPARTMENTS
execute PL/SQL code:
declare
v_emp varchar2(20000);
begin
select listagg(employee_id,':') within group (order by employee_name)
into v_emp
from table
where department_id = :EMP_DEPARTMENT
order by 1;
apex_util.set_session_state('EMPLOYEES_SHUTTLE', v_emp);
exception
when others then
apex_util.set_session_state('EMPLOYEES_SHUTTLE', null);
end;
execute JavaScript code:
var emps = document.getElementById('EMPLOYEES_SHUTTLE_LEFT').options;
var allValues = $v('CURRENT_EMP');
if ($v('STORE') == "" && $v('DEPARTMENT') == "")
{
for (index = 0, len = emps.length; index < len; ++index) {
allValues += emps[index].value.concat(":");
}
$s('EMPLOYEES_SHUTTLE',allValues);
}
else
{
sortSelectOptions('EMPLOYEES_SHUTTLE_RIGHT');
sortSelectOptions('EMPLOYEES_SHUTTLE_LEFT');
}
sortSelectOptions('EMPLOYEES_SHUTTLE_LEFT');
}
CURRENT_EMP is a page item that retrieves a colon separated list of all employee_id's under EMP_DEPARTMENT.
I think my biggest problem is in the JS code.
Can you help me with finding a way for retrieving both the left and right side of the shuttle at the same time, instead of displaying ONLY the left side or ONLY the right side?
APEX version: Application Express 19.1.0.00.15
Browser: Version 80.0.3987.116 (Official Build) (64-bit)