Forum Stats

  • 3,734,276 Users
  • 2,246,935 Discussions
  • 7,857,216 Comments

Discussions

APEX form to update/insert/delete data in Oracle table with an approval flow

User_MPPTF
User_MPPTF Member Posts: 5 Green Ribbon

Hi,

I am completely new to APEX development and I just installed APEX on our Oracle Exadata platform.

We have a requirement from our Users to be able to perform DML operation on a table and have an approval process before performing the DML on the table. Could you pls help me in the below requirement and guide the best possible solution and next steps for the same

Requirement -

User u1 needs to insert/update/delete on the data in one table (t1) and maintain a log for the changes made in table (t2). Here table t2 DDL will be table t1 + audit columns to store logs

Here, table t1 has 8 million records

There needs to be an approval process so that the changes posted in APEX form by user U1, first go through an approval process, and another user AU1 can either approve/reject those changes. If approved the changes will be performed on t1 and logs maintained in t2

Since multiple users could be making updates to this table we need to either allow parallel changes or allow only 1 user to access/change at a time. Also the actual USER ID of the user posting the changes should be stored in both tables t1 and t2 in one of the columns to trace back user

U1 would need to be able to see the data in t1 and decide if they need to make changes

Approver user AU1 should also be able to see the existing table t1 content


Your help is highly appreciated and at this point very crucial to get a headstart on this totally new requirement

Thanks in advance!

Comments

  • fac586
    fac586 Senior Technical Architect Member Posts: 19,676 Black Diamond

    Please update your forum profile with a recognisable username instead of "User_MPPTF".

    APEX is one of many features of the Oracle database. However it is practically irrelevant with respect to this requirement. In this case APEX would simply provide the UI, and the solution to the stated problems would be developed using another database feature, namely Workspace Manager.

  • User_MPPTF
    User_MPPTF Member Posts: 5 Green Ribbon

    hey I updated my name, thanks !

    Now. sorry in advance for my novice response but isnt APEX used to do DML on Oracle tables?

    I had found another link with part of my requirement(Approval process) in APEX forum and most of the people suggested it is possible in APEX

    I do think it is possible and based on the time constraints we have at hand I really do need to find a solution in APEX :(

  • Sylvain Martel
    Sylvain Martel Member Posts: 674 Bronze Trophy

    Hello User_MPPTF,

    If you are a novice with APEX and starting a project involving some workflow process, this is definately not a project for the faint of heart!

    APEX will allow you to develop an application to support a business process and maintain that information in Oracle tables, but the workflow part will have to come from another "tool", unless you just want to develop a workflow engine on your own with APEX (which can be done!).

    For workflow, I'm thinking of 2 options on the top of my head that will help you: Flows for APEX (Look here for info) and Oracle Integration Cloud (OIC). With these tool, you should be able to have APEX initiate your workflow processes and perform the appropriate transaction based on your approval rules.

    But my 2 cents advise for you, you should hire some help to get this project to the finish line on time, unless you have unlimited time "on the Google"...

    Hope this helps!

  • User_MPPTF
    User_MPPTF Member Posts: 5 Green Ribbon

    Hi Sylvain,

    Thanks, but may I know what's the simplest/bare minimum part of my full requirement that we could still implement in only APEX in matter of a month?

    For us., the most important requirement is to be able to make changes to the table as a bare minimum, we could do with not doing the approval process if it is 'that' complicated

  • Sylvain Martel
    Sylvain Martel Member Posts: 674 Bronze Trophy

    You could probably implement all the functionalities except workflow in a matter of days with APEX (of course based on my bias knowledge of APEX). A quick reading of your requirements seems very "regular" to me and APEX (along with the Oracle database of course) will have not problem handling the load and functionalities.

    The reason workflows can be 'that' complicated is based on the following typical requirements:

    Delegation / Vacations / Response delays and retries / Rejects and re-submit / Multi-vote / Error handling / Position hierarchy as opposed to named supervisor / approvals levels based on severity / Email responses / Mobile approval / etc.

    I'm used to develop APEX applications on top of Oracle E-Business Suite (ERP) so workflows rules and processes can become quite complicated.

    Hope this helps.

  • User_MPPTF
    User_MPPTF Member Posts: 5 Green Ribbon

    Hey so means the data manipulation is easy but the approval process could be complicated?

    Would you be able to share some tips or links which might have done something similar to my requirement that I could use as a base/learning to start my project?

  • Richard Legge
    Richard Legge Member Posts: 490 Bronze Badge

    There's lot of material available. Maybe start with the main documentation: https://apex.oracle.com/en/learn/documentation/ and tha app builders guide.. https://docs.oracle.com/en/database/oracle/application-express/20.2/htmdb/index.htm... Given your requirement. I'd start with a very simple application that does the required DML on the table. That (depending on additional requirements), is pretty straightforward and can be achieved by using the built in wizards.

    Personally, how I would approach the workflow in this scenario (and maybe I'm way off here), is by customising the process so that instead of APEX updating the table when told to do so, it copies the revised version to either a new table, or does an insert into the table with the revised data, and a flag to say it needs approval. (this could be achieved with an Instead Of trigger). A separate part of the APEX application, or separate application then deals with approving the change to the data (a set of pages that has a report on rows that need approval, and an update/change process that carries out the appropriate DML to delete the original record and change the new record to approved).

    As previously mentioned, it could get very complicated very quickly. You would (in addition to delegation/holidays/approval hierarchy), you would also need to deal with inserts/updates/deletes and deal with single or multiple rows etc.

    Just my two pennys worth..

    Sylvain MartelUser_MPPTF
  • Sylvain Martel
    Sylvain Martel Member Posts: 674 Bronze Trophy

    Hello again,

    To complement what Richard was providing, I would highly recommend you have a look at the Oracle APEX pages in the Education section. If you go at the bottom of that page, you will see a section called Application Development Curriculum where you can download an introduction course with slides and hands-on labs. It is very well done!

    It looks like this is the first step you need to take.

    Good luck!

    User_MPPTF
  • AndyH
    AndyH Member Posts: 644 Bronze Trophy

    User u1 needs to insert/update/delete on the data in one table (t1) and maintain a log for the changes made in table (t2). Here table t2 DDL will be table t1 + audit columns to store logs

    Here, table t1 has 8 million records

    There needs to be an approval process so that the changes posted in APEX form by user U1, first go through an approval process, and another user AU1 can either approve/reject those changes. If approved the changes will be performed on t1 and logs maintained in t2

    At the simplest level, it sounds like you need to 'stage' data in a table (TS) whilst it awaits approval and once approved it is moved into T1 and a log of the changes recorded in T2?

    If you are approving each row of TS separately you could simply add columns for status (e.g. waiting for first approval, waiting for second approval, approved, rejected, etc) and the users entering and approving the data. Once finally approved you make the update to T1 and record changes in T2?

    If you are approving/rejecting a batch of rows at a time, then you might have a separate table to hold the status information against the batch and have a foreign key from the staged data to the status table.

    You could implement a poor-mans workflow by generating emails, etc. on change of status and only allow relevant users to access the data as it moves through the approval process.

    User_MPPTF
  • User_MPPTF
    User_MPPTF Member Posts: 5 Green Ribbon

    I really appreciate everyone for their responses

    I shall start looking at some of the links provided and will revert back (hopefully soon) on y progress on this

  • InoL
    InoL Member Posts: 8,995 Silver Crown

    Most answers already point to several solutions, which basically have little to do with Apex, but your design. Apex is just a web page generator on top of your data and database logic.

    As AndyH commented: you can have a "stageing" table, where records are waiting for approval before moving the data to your T1 table.

    We do it by giving a record a status, like "Ready for Review", "Waiting for Approval" and "Approved". This is very generic and can be applied to multiple entities, like invoices, claims, vacation requests, service requests etc.

    An authorization scheme defines which users or roles can change a status, e.g. from "Waiting for Approval" to "Approved" (or any other status change).

    Logging of course is pretty easy. That can be done with a after insert/update/delete trigger on T1.

Sign In or Register to comment.