14 Replies Latest reply: Oct 2, 2014 3:15 AM by Ziut RSS

    Manage Master Detail Pages

    Ziut

      Hi

       

      My workspace: tb2/tb2/tb2

      App no: 10208

      Example of Master Detail Form

       

      I have two pages: 10 (Master) and 11 (Details),

       

      I have two db tables: demo_orders_jw (order_id PK,.....) and demo_order_items_jw (order_id PK and FK, position PK,.....), (I modified standard model).

       

      eg:

      table orders: (1,....), (2,....)

      table items: (1,1,....), (1,2,....), (1,3,....), (2,1,...),(2,2,....)...

       

      On Page 10 I select order (eg 2030) and go into Page 11 with order_id = 2030. This number is in item :P11_ORDER_ID.

       

      Next I want to use Add Row and I have problem with order_id in detail form.

       

      In this moment it doesn't work fine because in trigger tr_IU_order_items_jw in INSERTING section I have: select max(order_id).....

      It works fine if I want to add new items into last order only.

       

      Question is: Is it possible alter order_id in Detail Form with :P11_ORDER_ID without collection technology?

       

      Regards

      Ziut

        • 1. Re: Manage Master Detail Pages
          Ziut

          Hi experts

           

          Please help me and let me know if it is possible modify detail form element with item without collection technology?

          Ziut

          • 2. Re: Manage Master Detail Pages
            Ziut

            Hi

            I repeat my question:

            Is it possible modify detail form element with item without collection technology?

            Ziut

            • 3. Re: Manage Master Detail Pages
              TexasApexDeveloper

              WHY are you using this logic to get your next id: select max(order_id)?? Why are you NOT using a trigger to get this value?  In a multi-user environment you are just asking for trouble to do this..

               

              Also what is WRONG with using a collection with tabular forms??

               

              Thank you,

               

              Tony Miller
              LuvMuffin Software
              Ruckersville, VA

              • 4. Re: Manage Master Detail Pages
                Ziut

                ...for example:

                 

                in master table I have max order_id = 2103 but I would like update order = 2100 (details). In master report i click 2100 and Add Row and new detail.

                This new detail will be added not to 2100 but max  order_id because trigger...

                 

                My question is: How update order id (2100) in details form ?

                 

                Ziut

                • 5. Re: Re: Manage Master Detail Pages
                  Ziut

                  Tony

                  Why are you NOT using a trigger to get this value?

                   

                  Could you tell me - how?

                   

                  Ziut

                  • 6. Re: Manage Master Detail Pages
                    TexasApexDeveloper

                    Seems that you already have a trigger in place for the parent (master) table.. Now I would suggest getting one for the child table...  When you get the next row, you would just get the nextseq value of  the child table trigger sequence..

                     

                    If you were using a collection, this will allow you to do so much more..Look at this sample application (has a link to the article Jari wrote talking about using an interactive report to build a tabular form.. : http://glassfish1.revion.com/apex/f?p=102:1:13416633131076:::::

                     

                    Thank you,

                     

                    Tony Miller
                    LuvMuffin Software
                    Ruckersville, VA

                    • 7. Re: Manage Master Detail Pages
                      Ziut

                      Tony

                       

                      My model parent_child differs from standard oracle master_detail model.

                      I have tried to describe it in my first post.

                       

                      Master table has order_id as PK,

                      Detail table has composed PK: order_id and position.

                       

                      In standard model:

                      Detail table has order_id_item (I don't remember name) as PK.

                       

                      Run my application and pay attention on detail form. I have not order_id on tabular form but I have it in item :P11_ORDER_ID only.

                       

                      Look at trigger for detail table:

                       

                      create or replace trigger tr_IU_order_items_jw

                        before insert or update on demo_order_items_jw

                      for each row

                      declare

                        v_order_id demo_orders_jw.order_id%type;

                        v_position number;

                        v_unit_price demo_product_info.list_price%type;

                      begin

                      if INSERTING then

                         

                          select max(order_id) into v_order_id from demo_orders_jw;

                          if v_order_id is null then

                            :new.order_id := 1;

                            v_order_id := 1;

                          else 

                            :new.order_id := v_order_id;

                          end if;

                       

                      ............

                       

                      First line after if INSERTING..... is not good. Selected order_id should be in this place  not max order_id but question is - how?

                       

                      Your suggestion about sequence is not good and refer to standard oracle model not mine.

                       

                      Thanks for your attention

                      Regards

                      Ziut

                      • 8. Re: Manage Master Detail Pages
                        TexasApexDeveloper

                        So yo have a composite key on the detail portion of your detail form.. Okay, so again I will ask again, do you UNDERSTAND The concept of using a MAX() function in a trigger, in a multi-user environment?

                         

                        I think you need to work out your data model issues BEFORE you try building code to populate you tables, honestly...

                         

                        Thank you,

                         

                        Tony Miller
                        LuvMuffin Software
                        Ruckersville, VA

                        • 9. Re: Manage Master Detail Pages
                          Ziut

                          Tony

                          1. I understand the concept of using max function in multi-user environment and in single-user environment, too.

                          2. At present trigger doesn't work properly in single-user environment. I know it and I know - why.

                          3. My data model is constant and I can't change it.

                          4. I have tried to solve that problem without collection.

                           

                          Question is: It is possible in apex or not (change element of tabular form with apex item)? I would like to know.

                           

                          If no - I will try to find other solution. I have any idea.

                          Regards

                          Ziut

                          • 10. Re: Manage Master Detail Pages
                            Ziut

                            Hi

                            Because I had no answer my problem with master details form I've found my own solution.

                            I use work table where I insert row with current order id. And later I may modify details in trigger with it.

                             

                            You may run my application:

                             

                            apex.oracle.com

                            tb2/tb2/tb2

                             

                            Scenario:

                            1. login into workspace

                            2. select * from current_order_id - you should see only one row where session_id is null

                            3. run app=10208 and master details button

                            4. select any Order_No (from Page 10) - no max

                            5. on Page 11 you can see details this order

                            6. repeat select * from current_order_id - you should see row with current session number and order_id

                            7. on Page 11 Add row (product) and pay attention to Order Total after Apply Changes

                            8. Logout application

                            9. select * from current_order_id - you should see only one row where session_id is null.

                             

                            That's all.

                            Regards

                            • 12. Re: Manage Master Detail Pages
                              Ziut

                              Hi

                              The question is, what's the good you can do with it?

                              You inform us about your solution but how to do it?

                              Would you like to show software?

                              Ziut

                              • 13. Re: Manage Master Detail Pages
                                2736259

                                Email to author, he gives access to his demo workspace - it is just Apex and no software.

                                • 14. Re: Manage Master Detail Pages
                                  Ziut

                                  Sorry

                                  This is public forum. If you would like to discuss about your software give access for everyone.

                                  Ziut