I posted a while back regarding a use case where I need to integrate OTL in Oracle E-Business Suite (v12.1) with APEX (v5.1) to create some custom Timesheets.
Following on from this and initial investigations, I now need to assess how the requirement can be achieved in APEX.
The requirements are listed below and in conjunction with the attached image will help you to gain a better understanding of what it is that I am trying to achieve.
- Develop a custom timesheet page/application which will be built on top of Oracle E-Business Suite OTL. Therefore, it will be retrieving data from, and then in turn write back to, Oracle Time Store.
- Main header information at the top to show the dates and whether the overall status of the Timesheet is open or not. Page will contain 1 or more separate Timesheets. Can also save the data from here and also move through different pages (limit of approx 10 Timesheets per page) using pagination options and buttons at the top in this header section.
- Each Timesheet will belong to a different person and allow user to enter their time worked against a particular type of hours. The 'Hours Type' and 'Reason' need to be dependent dropdown menu options i.e. the option selected in 'Hours Type' will influence what is populated and available in the 'Reason' dropdown option.
- There will be a checkbox option to select each Timesheet in order to submit them using a 'Submit' button at the top.
- When outputting each Timesheet, will need to output a header section and then some lines for the user to enter hours. These lines may be pre-populated upon the page rendering depending on some rules.
- Need to be able to add lines to the bottom of each Timesheet using the '+' plus icon within the Timesheet, and maybe also a delete icon at the end of each row in order to delete a row in a Timesheet.
- Some columns need to be greyed out/disabled if a date falls outside of the month e.g. in this case, Sat 01 and Sun 02 fall outside of the month of August and should be disabled so user can't enter any time against these days.
- Because individual Timesheets can be submitted, any that have been submitted need to be greyed out/disabled but displayed, e.g. the one at the bottom of the picture.
- There will be a preceding Search page where user will enter parameters and that will determine how many Timesheets are going to be rendered in the Timesheet page. Also, as values are changing or being entered against the Timesheet, these will need to update some total columns.
Would the page be based on a single, overall SQL query or would I need to write a separate query for each table/form?
I appreciate your assistance with this and any advice that you can provide. Thanks for your patience.