APEX

Greetings, everyone,

I am new to exploring Oracle and have recently created an application that includes a Leave Management Module. The basic architecture of the application is as follows:

Application <> Manager Recommendation <> Partner Review <> HR

I have implemented a notification system located at the top right corner under from Shared Components > Application Computations > NOTIFICATION_MENU. This system displays the numeric count of notifications when users apply for an application.

Here is the code I used for the notification:

• SQL:

SELECT 
   COUNT(*) 
FROM 
   (
   SELECT 
       lt.tran_mst_id, 
       emp.staff_id,
       emp.employee_name,
       lt.PERIOD_FROM,
       lt.PERIOD_TO,
       lt.TOTAL_DAYS,
       lt.REPORTING_ON,
       ltt.leave_type_name,
       dsg.desig_name,
       dpt.dept_name,
       NVL(LT.MANAGER_APPROVE_FLAG,'A') AS MANAGER_APPROVE_FLAG,
       MANAGER_COMMENTS
   FROM 
       employee_info emp,
       tbl_hr_department dpt,
       tbl_hr_designation dsg,
       leave_tbl lt,
       leave_type_tbl ltt
   WHERE 
   1 = 1
   AND emp.staff_id NOT IN (4, 5, 6)
   AND emp.dept_id = dpt.dept_id (+)
   AND emp.desig_id = dsg.desig_id (+)
   AND lt.staff_id = emp.staff_id
   AND ltt.leave_type_id = lt.LEAVE_TYPE
   AND emp.status = 1
   AND (
       (NVL(MANAGER_APPROVE_FLAG, 'A') IN ('A', 'N') AND LT.MANAGER_STAFF_ID = :P1_STAFF_ID)
       OR
       (NVL(MANAGER_APPROVE_FLAG, 'N') = 'Y' AND NVL(LT.PARTNER_APPROVE_FLAG, 'A') IN ('N', 'A') AND LT.PARTNER_STAFF_ID = :P1_STAFF_ID)
   )
   ) subquery;

• Currently, the system works as follows:

When an applicant applies for a leave application, it is transferred to the manager's account. The notification count on the manager's account shows (1/2) until the manager reviews and recommends it. Then it moves to the partner's account, and the partner's notification count shows (1/2).

• What's not working:

1. Notifications are only working for managers and partners;
2. HR is not receiving notifications. Similarly, when an application is approved, users are also not being notified.

• What I want to do now:

I would like to create a web-based push notification system so that when an applicant applies, both managers, partners, HR and Users are notified. Additionally, I want to implement a browser-based push notification system that will automatically display notifications on the desktop screen.

I kindly request support and help from experts. It would be beneficial to have a discussion over Google Meet to provide a more detailed understanding of the system and receive assistance in achieving my goals.

System Information:
Application Express 20.1.0.00.13