End-User facing expression builder in APEX?

AllenS.

Hi,

I've built a sort of Document confirmation page for employees. The document contains clauses specific to an employee. The document will be defined by admins as well as clauses which are mainly the content of a document. Kind of like a contract.

Each clause are employee specific. Meaning a certain clause will be available to some employees only. So some employees will have certain clauses in the document they received/see via page while others do not.

I'm having some difficulty on this part as I am not yet sure what's the best way to do this. Currently I am using an Interactive Report (using employees table) where I hide everything except the Filter menu then let the admins set all the filters there for each clause record. I then get the SQL of the said Interactive Report and parse the where clause of the SQL statement. Then I can just fetch this where clause and concatenate it as needed. One issue I have here is where the user wants to edit the current filters which I will need to programmatically add filters to the IR. Not sure how to do that yet.

Another approach would be to manually create fields (select list, checkboxes etc) for each of the columns required for the filter but that would be my last resort.

I would like to know if there's a better approach to this?

Were on APEX 18.2.

Appreciate any feedback.

fac586

I've built a sort of Document confirmation page for employees. The document contains clauses specific to an employee. The document will be defined by admins as well as clauses which are mainly the content of a document. Kind of like a contract.

Each clause are employee specific. Meaning a certain clause will be available to some employees only. So some employees will have certain clauses in the document they received/see via page while others do not.

I'm having some difficulty on this part as I am not yet sure what's the best way to do this. Currently I am using an Interactive Report (using employees table) where I hide everything except the Filter menu then let the admins set all the filters there for each clause record.

That was my first thought when I read the thread subject line: it's immediately available and familiar to users.

It might help to switch to an IR detail view that provides a better presentation of text content than the default columns.

18.2 lacks the newer built-in filtering capabilities provided by Faceted Search and Smart Filters, which may or may not have been of use depending on the type(s) of filters required.

I then get the SQL of the said Interactive Report and parse the where clause of the SQL statement. Then I can just fetch this where clause and concatenate it as needed.

Now I start to get rather confused as to what is happening. Please clarify the problem. Are you saying that the admins only define the filters and you save and use these subsequently to restrict the information shown to groups of employees? It's not that the admins use the filters to select clauses to create and store employee-specific documents?

If the former then be aware that approach may become more difficult in future APEX releases:

https://community.oracle.com/tech/developers/discussion/comment/16845191#Comment_16845191

I would like to know if there's a better approach to this?

We need a clearer description of the problem, and an indication of the following:

• Type(s) of filter(s) used
• Overall number of document clauses involved
• Number of clauses per document
• Text length of clauses and documents
• Organisation of clauses and documents (e.g. hierarchies, paragraph numbering etc)
• Number of employees and any relevant organisational details

AllenS.

Hi,

Now I start to get rather confused as to what is happening. Please clarify the problem. Are you saying that the admins only define the filters and you save and use these subsequently to restrict the information shown to groups of employees? It's not that the admins use the filters to select clauses to create and store employee-specific documents?

I will get the filters and store it on the same record as the clause itself. Clauses are defined ahead of time before employees will have the access to specific documents. Employee data can still changed where an employee will get promoted and get a higher salary grade which means a document clause may have not been available initially but is now after the promotion or upon the availability of the document.

create table hr_clauses_t (
    id number primary key
  , clause clob
  , filters clob -- Stores the filter/where clause from the IR
)

--Additional tables for reference.

create table hr_documents_t (
    id number primary key
  , title varchar2(250)
  , start_date date
  , end_date date
  -- and so on
)

create table hr_document_clauses_t (
    id number primary key
  , document_id number --foreign to hr_documents_t
  , clause_id number --foreign to hr_clauses_t
  , sequence_no number
)

-- Sample table structure only
create table hr_employees_t (
    id number primary key
  , employee_id number
  , first_name varchar2(80)
  , last_name varchar2(80)
  , salary_grade number
  , department varchar2(100)
  , division varchar2(100)
)


If the former then be aware that approach may become more difficult in future APEX releases:

If this is the case, I would rather not use the apex_ir.get_report function. Worst case, might as well build our own expression builder.

Type(s) of filter(s) used

Mostly regarding salary grade, department, division etc. Certain clause will be available for employees with this salary grade or on this department, division. So something like:

where salary_grade >=5

Overall number of document clauses involved

Hard to say. There will be a table where admins define clauses separately. Sort of like a library of document clauses. Then another table for defining a document (with title etc.), then another table for document clauses where admins select clauses that will be linked to a document.

Number of clauses per document

This will vary. Can be 3 or more depends on the document. I would guess that an employment promotion contract type document will have more clauses versus a waiver type document.

Text length of clauses and documents

The data type for clause field is CLOB. But in reality, I would think 4000 is enough. The only thing that may make this bigger is the formatting (HTML tags). The page where admins will define document clauses uses a Rich Text Box which gives them the ability to format the clauses based on their preference.

Organisation of clauses and documents (e.g. hierarchies, paragraph numbering etc)

No hierarchies. Paragraph number will be defined by the admins. During the creation of the document, admins can define the sequence of the clauses that they insert in the document.

Number of employees and any relevant organisational details

Around 3000 employees more or less. Basically when a staff opens the page, it contains the employee id. Then on the page, I'd probably use a PL/SQL Dynamic Content region where I loop over the clauses and validate the where clause of the document clause against the employee record. This will determine whether the document clause will be shown or not.

Thanks.