This discussion is archived
11 Replies Latest reply: Dec 4, 2013 5:20 AM by pawankumaar RSS

Row level cascading in Apex tabular form

pawankumaar Newbie
Currently Being Moderated

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 Explorer
    Currently Being Moderated

    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
    pawankumaar Newbie
    Currently Being Moderated

    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
    pawankumaar Newbie
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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
    pawankumaar Newbie
    Currently Being Moderated

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

     

    Thanks,

    Venkat

  • 6. Re: Row level cascading in Apex tabular form
    pawankumaar Newbie
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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
    pawankumaar Newbie
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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
    pawankumaar Newbie
    Currently Being Moderated

    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
    pawankumaar Newbie
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points