I have a requirement related to oracle Apex Interactive Grid. I have role table that assigns roles to users as well as departments.
So the user with Dept A, will be able to see Dept A data and so on.
SO in my application on one page, i have 2 items.
P1_DEPT -> Select list -> select dept_name as d,dept_id as r from depts
P1_PROJECTS -> Shuttle item -> select p_name as d, p_id as r from projects
These dept and respective projects selected via shuttle get stored in the table: P_DEPT_PROJECTS.
It looks something like this:
Now on the second page i need to create a grid from table which has all the projects as columns.
Table:
select p_pk,project_1,project_2,project_3,poject_4,project_5,project_6,project_7,project_8,project_9,project_10,project_11 from final_data;
However the condition for creating this grid would be:
The columns would show based on the projects assigned departmentwise in the P_DEPT_PROJECTS table.
I.e. if, DEPT_ID=1 or DEPT_NAME=A has 2:3:4, i.e. Project 2, project 3, Project 4 allocated.
The query would be made dynamic such that it only shows the 3 columns data.
The result would be select project_2,project_3,project_4 from final_data where :APP_USER in(select user from role where dept = :DEPT);
So i need help with formulating the grid query that needs to be made dynamic such that it picks the shuttle values stored department wise, converts them into columns : 2 > Project 2> Project_2 and show data accordingly.
Apex 20.2