10 Replies Latest reply on Oct 16, 2018 1:17 PM by BillUK

    How to create multiple independent dynamic forms on a single page

    BillUK

      Hi,

       

      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.

       

      In summary, I'm not sure how to go about developing this and whether this use case is indeed achievable using APEX. I require some guidance on which development approach to take and the key features/development practices in APEX that I will need to make use of. It looks to me like I need to create multiple forms on a page which behave independently and maintain a handle on the data for each of these to then be able to submit back to OTL. Will I have to use extensive Javascript and AJAX? Can the existing templates or wizards suffice or will I need to create and render all components dynamically at the page rendering stage using custom code? E.g. in Oracle ADF, when dealing with collections of data, you can create components like tables etc dynamically using an Iterator component.

       

      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.

       

      MyTimesheet.JPG

        • 1. Re: How to create multiple independent dynamic forms on a single page
          BillUK

          Hi,

           

          I was just wondering whether anyone has any ideas on this or can point me in the correct direction please?

           

          Many thanks.

          • 2. Re: How to create multiple independent dynamic forms on a single page
            Fernando Lima

            This case is similar to surveys.

            Probably you have to use apex_item package to create item by item, it's a large work to do because you'll need some trick like web services to save them.

             

            Fernando.

            • 3. Re: How to create multiple independent dynamic forms on a single page
              J-Lig

              We just moved off of OTL (for the most part) and onto custom timesheets created in APEX. OTL did not work out for our organization at all and we could never find anyone who knows how to update the templates, etc. We have hired at least 10 different consultants from all different organizations (including Oracle) who all claim to have the skills/knowledge but none have been successful so far. Needless to say, we have NOT been happy with OTL from any aspect. 

               

              Anyway, I think the answer to your question is that pretty much anything is possible in APEX. The level of effort depends entirely on how complicated your app and processes are. If you are building on top of OTL, it will be EXTREMELY complicated. We did similar analysis a year or two ago, and I would say from personal experience, I would NOT attempt to build anything on top of OTL. OTL has a really bad structure as it is and building any kind of clean, modern app on top of something so horrible would be much more work than starting clean with your own design. That said, you can build quite nice timecards with the new Interactive Grid. It has many of the features you have described already built-in (adding rows, editable/disabled fields, multiple timesheets, select records to submit, etc). We also built a calendar view of the timecards for the supervisors so they can view their employee's time/vacation, etc. It turned out really nice and super easy to use.

               

              I would strongly recommend (for your own sanity) that you not build on top of OTL. It does not work in any way like any other Oracle product. I think they clearly bought it from another company and then integrated it into their suite. Everything in there is SO overly-complicated, and SO un-intuitive and the database design is HORRIBLE. We have used it for a few years now and already have several million records in multiple tables.

               

              Not sure if you can private message on here, but if you can, I am happy to share our struggles with you.

               

              Jen

              • 4. Re: How to create multiple independent dynamic forms on a single page
                BillUK

                Fernando/Jen,

                 

                Many thanks for sharing your ideas and thoughts in relation to my requirements.

                 

                So, I take it from your replies that it will be possible to have multiple timesheet forms on a page? If so, will these be tabular forms or Interactive Grids? Is it possible to use IRs and get them to look like the designs in my attachment?

                 

                How can I handle/process the data for the different timesheets? Should I retrieve the data from the OTL source and bring each timesheet's data into individual APEX collections (can you even have multiple collections on a page?) OR should I be putting the data into a custom/temporary table and process and write back to that table, before writing back to OTL using the relevant APIs?

                 

                Regarding going down the APEX_ITEM route, are there any limitations or pitfalls that I need to be aware of e.g. I have read something about a 50 column limit, would this apply here? Can you manually apply things like disabled/read-only properties on components that have been created using APEX_ITEM?

                 

                I'm also wondering whether APEX is actually capable and suitable for this task and whether ADF would have been more suitable.

                 

                I'm not sure if I am thinking about all of this the correct way and welcome any suggestions for different approaches.

                 

                Jen - thanks for your response regarding OTL. I would be very interested in learning about your investigations etc. I'm not sure if you can private message through here. I will try, but if not, then I would like to get in touch to discuss your experience. Unfortunately, the requirement is to definitely integrate the page/application with OTL and from what you've said and what I have read so far, it seems like a really clunky task, be that due to the structure of OTL or achieving the UI using APEX.

                 

                Appreciate your assistance with this and am grateful for all of your help.

                • 5. Re: How to create multiple independent dynamic forms on a single page
                  J-Lig

                  Bummer! Well, it certainly can be done. APEX can do it. I'm sure ADF can do it too. APEX maybe easier though and you wont have to have the overhead of java programming/support. The difficult part wont be creating the app though. The difficult part would be integrating with OTL. The degree of difficulty there would be the same, regardless of which route you choose. I think OTL has an API. Let me check. It may not be as difficult if you can use an API. Otherwise, there are MANY moving parts to OTL and the data is spread across lots of tables so you will have to know about and address each of those. Plus the integration with HR and anything else you might have. That will be the most difficult part of the project. Building the app pages should be relatively easy.

                   

                  We have other apps that currently integrate with OTL and the biggest problem we have so far is performance. As I mentioned before, we have several million records in several of the timecard tables due to the method OTL uses for maintaining history. Every time users make changes to timecards the record count grows exponentially. We are currently looking into other arrangements for this now. Possibly adding partitions to some of the tables.

                   

                  We used to use IR with apex_item before they created IG. You can use IR with apex_item, but you would basically just be building the same thing that IG already does for you. It would be a LOT more work. Writing custom row processing is also much easier with the new Interactive Grid. They basically took what you could build manually before with IR/apex_item and did all the work for you.

                   

                  Jen

                  • 6. Re: How to create multiple independent dynamic forms on a single page
                    J-Lig

                    The other major factor that will make things difficult is if you have complex leave accrual/usage calculations/validations. We have MANY leave accrual calculations and validations as well as extensive time validations. How do you plan to handle those?

                    • 7. Re: How to create multiple independent dynamic forms on a single page
                      BillUK

                      Hi Jen,

                       

                      It's somewhat refreshing to know that it can at least be done! I've worked with ADF and Java in the past hence why I would probably have preferred it to be done using that. It seems to me that the APEX approach will also involve a large overhead of programming in PL/SQL but also JavaScript, JQuery and AJAX with which I am a relative novice. But I guess I better get on top of it!

                       

                      Re OTL, I've researched the APIs that will allow me to create/update/delete timesheets in OTL in the HXC/HXT but yes it is a very complex data model which I am trying to get my head round. There will be some difficulty in retrieving the data in the first place as it's spread out across many tables as you mentioned.

                       

                      I've not considered the performance side of things yet and due to the way the data is held I can see where you are coming from. I guess if we are stuck with having to use OTL then we will have to introduce partitions or something.

                       

                      What's the difference between IR and IG? I have worked with IRs in the past but not editable ones. If going down the IG route, can we have multiple IGs on a page which represent the different timesheets, or would it work differently to that? Is it easy to customise the way they work e.g. when editing the time for a row in a timesheet, can we have open text fields that users can input straight into, or would you need to click on a button in order to go into Edit mode?

                       

                      We will also have complex accrual/validations and time validations too and these are going to be attached to OTL Time Entry Rule Groups. So, before inserting timesheet data into OTL using the APIs, we would somehow need to be able to call the Time Entry Rule Group to carry out the validations (I need to also figure this out!!).

                       

                      Appreciate your help with this!

                      • 8. Re: How to create multiple independent dynamic forms on a single page
                        BillUK

                        Hi,

                         

                        I was wondering if anyone had any further thoughts on this please?

                         

                        I raised a question in another thread (How to dynamically generate multiple IGs on a single page ) about the possibility of dynamically having several IGs on one page but I have been told that it is not possible to dynamically create multiple regions (e.g. in this case, I was thinking of having 1 IG region per timesheet) in which case the use of IGs for this case might not be feasible. Is there an alternative approach to achieving this i.e. maybe doing it all within a single region, or having to manually create tabular forms etc.

                         

                        Appreciate your assistance with this.

                         

                        Regards

                        • 9. Re: How to create multiple independent dynamic forms on a single page
                          John Snyders-Oracle

                          HI,

                          I'll throw out a few thoughts. You ask about multiple dynamic forms on a page but the time sheets look more like editable reports to me in that they have multiple rows. The UI looks very custom and I don't think you will find any exact fit in any framework widget set. The interactive grid can be customized but I'm not sure it can exactly match the picture you showed. I recommend that you use APEX 18.2 if using interactive grid. Check out the latest IG Cookbook for customization ideas. APEX IG Cookbook Update for 18.2 – HardLikeSoftware  Of particular interest is the Tabbed Record Editing which shows how to use the IG model independent of the IG UI. Using the IG model to fetch and store data to APEX you can then use any kind of templating and DOM rendering to create exactly the UI you want.

                           

                          Are all the time sheets the same in terms of schema and UI. Can you think of it as one editable time sheet report on the page but with a control break on the user and you get to select the set of users. This changes the problem from having multiple (up to 10) dynamic regions to having just one. But either way the time sheet UI will need a lot of customization.

                           

                          Since you have a reasonable upper bound you could create 10 identical regions and then on the client side just show and hide them as needed.

                           

                          Regards,
                          -John

                          • 10. Re: How to create multiple independent dynamic forms on a single page
                            BillUK

                            Hi John,

                             

                            Many thanks for your response to my query and for your thoughts.

                             

                            Yes, I agree in that they are probably more like editable reports than a form. The UI is very custom and although we could be flexible in terms of adapting it to APEX (which may actually look better anyway!) we are governed by the general layout structure as that is how the data needs to be displayed really. I am investigating a slight change to the design which could make my life easier, and maybe provide a more intuitive experience for the end user. This is to split the page in two and on the left be able to select employees and assignments, and then for their respective timesheets to appear on the right hand side in the form of an editable IG with the header section at the top. So a Master-Detail format pretty much, and display a single time sheet which will change dependent on the user clicked on the left hand side. Obviously there will still be a good element of customisation involved to achieve the desired layout and behaviour.

                             

                            We have upgraded to APEX 18.2 and so I will check out the latest Cookbook (which is an excellent source by the way!) for some ideas and appreciate the link to that.

                             

                            The time sheets are all the same in terms of schema (coming from a two-table view) and UI and I did think about having it as a single editable time sheet report on the page with control breaks. My only reservation (likely due to a lack of understanding!) about this approach was that you would always see the control break group condition between each group. Whereas I would need to be able to customise this extensively to display something more akin to the header section of each time sheet in my picture which provides things like static text entries, running totals and possibly button links, all repeating for each group. Secondly, I presume the IG would have a single 'add row' option at the top of the grid which will add a new row right at the bottom of the result set, as opposed to an approach where you have an 'add row' option available within each time sheet header which will allow you to insert the new row at the bottom of the appropriate time sheet/control break group i.e. in between the result set at a particular point?

                             

                            The upper bound of 10 was an average, however this could be a lot more and it depends on how many employees fall under a particular manager. So we will never know that upfront.

                             

                            Once again, I really appreciate the time you've taken to respond to my query.

                             

                            Regards,