11 Replies Latest reply: Dec 4, 2013 7:20 AM by pawan kumaar RSS

    Row level cascading in Apex tabular form

    pawan kumaar

      Hi,

       

      I have searched alot,but i am not able to find the solution

       

      I have a tabular form and  having only one column say column A is dynamic LOV (Select col1 from lov_table will return values as 1,2,3........)

       

        When user press ADD ROW button

      row 1 => column A display LOV as _1,2,3.... then if user selects  1


      When user press ADD ROW button

      row 2 => column A "should" display LOV as 2,3,.....



       

      Anyone could help me out to solve this problem......



      Regards,

      Venkat



       



        • 1. Re: Row level cascading in Apex tabular form
          LA-APEX-DEv

          You can create a collection ('USER_SEL_COL') to store the selected value and in your LOV has a condition of value not exists.


          For example, your LOV query is:

          select number_value d,number_value r

          from number_table n

          where not exists(

          SELECT c001 --supposed this is the collection column you store the value the user selected

            FROM apex_collections e

          WHERE collection_name = 'USER_SEL_COL'

            AND  c001 = n.number_value)

          • 2. Re: Row level cascading in Apex tabular form
            pawan kumaar

            Thanks for your solution...

             

            Actually in my scenario  dynamic lov is based on 2 columns which are apart from tabular form region that mean

             

            i have a page with 2 forms ..one is form based on table and second is tabular form

             

            in tabular form i m using this dynamic lov   col

             

            Select Employee_number d,person_id r

            from Employee

            where person_id = :col1(which is 1 st col from page 1 form based on table     (not the tabular form col )    )

            and person_id =:col2(which is 2 nd col from page 1 form based on table         (not the tabular form col )    )

             

             

            for this i used a hidden item(select list) in that i wrote the above mention lov syntax 

             

            and assign that  hidden page item to that particular tabular form lov column

             

            that lov is working fine with the data which is saved in  db(ie in update mode)  

             

            but it is not changing values if i change the values of  (form based on table) form  (ie in create mode)

             

            to dynamically change tabular form lov which is based on other columns of other region what i have to do.......

             

             

            Thanks,

            Venkat

            • 3. Re: Row level cascading in Apex tabular form
              pawan kumaar

              Apart from Creating collection is there any other way to solve this problem using java script or j query

               

               

              Thanks,

              Venkat

              • 4. Re: Row level cascading in Apex tabular form
                LA-APEX-DEv

                If I'm not misunderstanding you, try to create a dynamic action based on the hidden item value change and refresh a 2nd region.

                • 5. Re: Row level cascading in Apex tabular form
                  pawan kumaar

                  Ya i tried it also but it doesn't worked out...

                   

                  Thanks,

                  Venkat

                  • 6. Re: Row level cascading in Apex tabular form
                    pawan kumaar

                    DECLARE

                      v_collection_name     apex_collections.collection_name%TYPE := 'XX_COLLECTION';

                      v_reset_flag               VARCHAR2 (1)                               := 'N';

                    BEGIN

                           IF apex_collection.collection_exists (p_collection_name => v_collection_name) = FALSE OR v_reset_flag = 'Y'

                           THEN

                      

                         APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY (P_COLLECTION_NAME => 'XX_COLLECTION'

                                                                      , p_query => q'! SELECT  employee_no,person_id FROM employee !'

                                                                      , p_generate_md5 => 'YES' );

                      END IF;

                    END;

                     

                    Is this the correct syntax for creating collection....

                     

                     

                    select number_value d,number_value r

                    from number_table n

                    where not exists(

                    SELECT c001 --supposed this is the collection column you store the value the user selected

                      FROM apex_collections e

                    WHERE collection_name = 'USER_SEL_COL'

                      AND  c001 = n.number_value)


                    In this  (  c001         --supposed this is the collection column you store the value the user selected)     where i need to mention this column

                     

                    If u have any inputs ,kindly suggest me ...

                     

                     

                    Thanks,

                    Venkat

                    • 7. Re: Row level cascading in Apex tabular form
                      LA-APEX-DEv

                      Since I'm not clear understanding what you're asking for, I would suggest you create a workspace and application on OTN. And I will see what I can do to help.

                      • 8. Re: Row level cascading in Apex tabular form
                        pawan kumaar

                        Hi,

                         

                        I created apex collection like XX_CDOT_MGPS_COL

                         

                        syntax like

                         

                        begin

                            APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY (P_COLLECTION_NAME => 'XX_CDOT_MGPS_COL' ,

                            p_query => q'! SELECT DISTINCT person_id  FROM employee !' ,

                            p_generate_md5 => 'YES' );

                        end;

                         

                        in apex sql workshop instance

                         

                        i used

                         

                        SELECT DISTINCT a.employee_no  d,

                          a.person_id r

                        FROM employee a

                        WHERE NOT EXISTS

                          (SELECT e.c001

                          FROM apex_collections e

                          WHERE e.collection_name = 'XX_CDOT_MGPS_COL'

                          AND e.c001              = a.person_id

                          )

                         

                         

                        i mentioned above lov in list of values column for that particular tabular form

                         

                        but it is not working ...

                         

                        kindly help me to solve this problem

                         

                        Thanks,

                        venkat

                         

                         

                        • 9. Re: Row level cascading in Apex tabular form
                          LA-APEX-DEv

                          Go here --> http://apex.oracle.com/pls/apex/f?p=TEST_APEX_COL

                          User Name: APEX_COL_TESTER

                          Password: test

                           

                          Is that what you're looking for?

                          • 10. Re: Row level cascading in Apex tabular form
                            pawan kumaar

                            Hi Friend ,

                             

                            After a long time i msg u  regrading same issue(how to restrict  the pop up  list of tabular form column dynamically based on values of the same column in apex)

                             

                            i am just again saying the requirement

                             

                            Example : i have  a column like item_id in tabular form

                             

                            If i selected one item from material table

                            in next row it wont be come in select list ...

                             

                            ur last reply regarding the prob is some wat helpful for me can u plz post the script for ur sample application it ll be very useful for me...

                             

                             

                            Thanks

                            • 11. Re: Row level cascading in Apex tabular form
                              pawan kumaar

                              Hi,

                               

                              steps i followed:---

                               

                              i created a dynamic action

                              event--- change

                              select type --jquery selector

                              jquery selector--[name="f05"]

                               

                              action--Execute PL/SQL Code

                              script---

                               

                              DECLARE

                                V_COLLECTION_NAME VARCHAR2 (30) := 'XXBCT_GPMS_COL';

                                V_ITEM_ID         NUMBER;

                                L_SEQ_ID          NUMBER;

                                L_ITEM_VALUE      NUMBER;

                              BEGIN

                                IF NOT apex_collection.collection_exists (p_collection_name => v_collection_name) THEN

                                  APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION (P_COLLECTION_NAME => V_COLLECTION_NAME);

                                END IF;

                                V_ITEM_ID := (APEX_APPLICATION.G_X01);

                                SELECT MIN(seq_id)

                                INTO L_SEQ_ID

                                FROM APEX_COLLECTIONS

                                WHERE COLLECTION_NAME = V_COLLECTION_NAME;

                                IF L_SEQ_ID          IS NULL THEN

                                  APEX_COLLECTION.ADD_MEMBER ( P_COLLECTION_NAME => V_COLLECTION_NAME, P_C001 => V_ITEM_ID );

                                END IF;

                                FOR i IN 1..apex_application.g_f05.count

                                LOOP

                                  SELECT SEQ_ID,

                                    C001

                                  INTO L_SEQ_ID,

                                    L_ITEM_VALUE

                                  FROM APEX_COLLECTIONS

                                  WHERE COLLECTION_NAME =V_COLLECTION_NAME;

                                  IF L_ITEM_VALUE      !=V_ITEM_ID THEN

                                    APEX_COLLECTION.ADD_MEMBER ( P_COLLECTION_NAME => V_COLLECTION_NAME, P_C001 => V_ITEM_ID );

                                  ELSE

                                    APEX_COLLECTION.UPDATE_MEMBER ( P_COLLECTION_NAME => V_COLLECTION_NAME, P_SEQ => L_SEQ_ID, P_C001 => V_ITEM_ID);

                                  END IF;

                                END LOOP;

                              END;

                               

                              affected elements-->no

                               

                              event scope--dynamic

                               

                              --->for tat tabular form column

                               

                              SELECT DISTINCT msib.DESCRIPTION,

                                msib.INVENTORY_ITEM_ID

                              FROM MTL_SYSTEM_ITEMS_B MSIB,

                                ORG_ORGANIZATION_DEFINITIONS OOD

                              WHERE MSIB.ORGANIZATION_ID      =OOD.ORGANIZATION_ID

                              AND OOD.BUSINESS_GROUP_ID       =FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')

                              AND MSIB.INVENTORY_ITEM_ID NOT IN

                                (SELECT AC.C001

                                FROM APEX_COLLECTIONS AC

                                WHERE AC.COLLECTION_NAME ='XXBCT_GPMS_COL'

                                )

                              ORDER BY msib.INVENTORY_ITEM_ID

                               

                               

                              but this script is not working ...

                               

                              collection itself not creating i don't know y..

                               

                              kindly help me to solve this problem....................

                               

                              Thanks,

                              Venkat