Make a Report that Can Edit another Report in APEX

Question

I have multiple report pages. I am working on a system that stores a lot of data. Is it possible to have reports that are able to have column edited on two different reports?

i.e. Not all the information may be relevant to the person entering data on the interactive grid. So I would have a condensed version of the report where the would be able to enter the data you are responsible for. But the report would still display the information entered on the condensed report version.

Answer 1

I'd think of a view and its instead of trigger.

Here's an example; see if you can adjust it to interactive grid. It is based on Scott's sample EMP and DEPT tables.

First, a view that joins these two tables:

SQL> create or replace view v_emp_dept as
  2    select d.deptno, d.dname, e.empno, e.ename, e.job, e.sal
  3    from emp e join dept d on d.deptno = e.deptno;

View created.

Instead of trigger; it fires when you update the view, but in the background it modifies data in view's underlying tables:

SQL> create or replace trigger trg_iu_ved
  2    instead of update on v_emp_dept
  3    for each row
  4  begin
  5    update dept d set
  6      d.dname = :new.dname
  7      where d.deptno = :new.deptno;
  8
  9    update emp e set
 10      e.ename = :new.ename,
 11      e.job = :new.job,
 12      e.sal = :new.sal
 13      where e.empno = :new.empno;
 14  end;
 15  /

Trigger created.

OK, let's test it. Sample data:

SQL> select * from v_emp_dept where deptno = 10;

    DEPTNO DNAME               EMPNO ENAME      JOB              SAL
---------- -------------- ---------- ---------- --------- ----------
        10 ACCOUNTING           7782 CLARK      MANAGER         2818
        10 ACCOUNTING           7839 KING       PRESIDENT       5750
        10 ACCOUNTING           7934 MILLER     CLERK           1495

Update some values:

SQL> update v_emp_dept set
  2    dname = 'Accounting',
  3    ename = initcap(ename),
  4    sal = sal * 10
  5    where deptno = 10;

3 rows updated.

Oracle says that 3 rows were updated:

SQL> select * from v_emp_dept where deptno = 10;

    DEPTNO DNAME               EMPNO ENAME      JOB              SAL
---------- -------------- ---------- ---------- --------- ----------
        10 Accounting           7782 Clark      MANAGER        28180
        10 Accounting           7839 King       PRESIDENT      57500
        10 Accounting           7934 Miller     CLERK          14950

The view looks OK. What about EMP and DEPT tables?

SQL> select * from dept where deptno = 10;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 Accounting     NEW YORK

SQL> select empno, ename, job, sal from emp where deptno = 10;

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7782 Clark      MANAGER        28180
      7839 King       PRESIDENT      57500
      7934 Miller     CLERK          14950

SQL>

Right; data has been changed in these two tables as well.