Sign Up

❇️ OrclQA.Com is a question and answer forum for programmers.
❇️ Here anyone can ask questions and anyone can answer to help others.
❇️ It hardly takes a minute to sign up and it is 100% FREE.

Have an account? Sign In


Have an account? Sign In Now

Sign In

Forgot Password?

Don't have account, Sign Up Here

Forgot Password

Lost your password? Please enter your email address. You will receive a link and will create a new password via email.

Have an account? Sign In Now
Sign InSign Up

OrclQA.Com

OrclQA.Com Logo OrclQA.Com Logo

OrclQA.Com Navigation

  • Oracle
  • Oracle Apex
  • Python
  • Questions
Search
Ask A Question

Mobile menu

Close
Ask a Question
  • Ask Question
  • Add Post
  • Oracle
  • Oracle Apex
  • Python
  • Questions
Swarup
Swarup

Swarup

  • India
  • 30 Questions
  • 119 Answers
  • 0 Best Answers
  • 202 Points
View Profile
  • 0
SwarupProfessional
Asked: November 2, 20202020-11-02T15:59:15+05:30 2020-11-02T15:59:15+05:30

Approve all dynamically in oracle apex

  • 0

Hi Sir,

I have created a lov with Approve All,Reject All.

If i select approve all then all the status field should be approved vice versa dynamically

dynamic
  • 16
  • 182
  • 0
  • 0
  • Share
    • Share on Facebook
    • Share on Twitter
    • Share on LinkedIn
Answer

16 Answers

  1. Vinish Kapoor

    Vinish Kapoor

    • 1 Question
    • 727 Answers
    • 63 Best Answers
    • 4k Points
    View Profile
    Vinish Kapoor Enlightened
    2020-11-02T16:08:55+05:30Added an answer on November 2, 2020 at 4:08 pm

    Create a dynamic action on the status LOV for the change event to execute PL/SQL code.

    Then add the following PL/SQL code in it:

    if :px_status_lov = 'APPROVE ALL' then
      Update table1
        set status = 'APPROVED'
      Where yourconditionColumn = :px_column;
      
      Delete from table1
        where yourconditionColumn = :px_column;
    elsif :px_status_lov = 'REJECT ALL' then
      Update table1
        set status = 'REJECTED'
        Where yourconditionColumn = :px_column;
    End if;

    Then create one more True action to refresh the report region below.

    • 0
    • Share
      Share
      • Share on Facebook
      • Share on Twitter
    • Swarup

      Swarup

      • India
      • 30 Questions
      • 119 Answers
      • 0 Best Answers
      • 202 Points
      View Profile
      Swarup Professional
      2020-11-02T16:28:41+05:30Replied to answer on November 2, 2020 at 4:28 pm

      Sir its not changing

      • 0
      • Share
        Share
        • Share on Facebook
        • Share on Twitter
      • Vinish Kapoor

        Vinish Kapoor

        • 1 Question
        • 727 Answers
        • 63 Best Answers
        • 4k Points
        View Profile
        Vinish Kapoor Enlightened
        2020-11-02T16:34:08+05:30Replied to answer on November 2, 2020 at 4:34 pm

        What is the return value of the LOV list P3_STATUSALL? I have given the example of APPROVE ALL in capital letters. You need to specify the only value you have defined for the return value.

        In items to submit, why P3_STATUSM? You should specify only P3_STATUSALL and P3_PID.

        • 0
        • Share
          Share
          • Share on Facebook
          • Share on Twitter
        • Swarup

          Swarup

          • India
          • 30 Questions
          • 119 Answers
          • 0 Best Answers
          • 202 Points
          View Profile
          Swarup Professional
          2020-11-02T16:48:03+05:30Replied to answer on November 2, 2020 at 4:48 pm

          Return Value of P3_STATUSALL is Approve All & Reject All

          when i click on approve all then  P3_STATUSM value should be approved (and  P3_STATUSM is also a lov containing Approved and Rejected

          • 0
          • Share
            Share
            • Share on Facebook
            • Share on Twitter
          • Swarup

            Swarup

            • India
            • 30 Questions
            • 119 Answers
            • 0 Best Answers
            • 202 Points
            View Profile
            Swarup Professional
            2020-11-02T16:57:53+05:30Replied to answer on November 2, 2020 at 4:57 pm
            This answer was edited.

            Its not firing where i m doing mistake plz let me know

            • 0
            • Share
              Share
              • Share on Facebook
              • Share on Twitter
  2. Vinish Kapoor

    Vinish Kapoor

    • 1 Question
    • 727 Answers
    • 63 Best Answers
    • 4k Points
    View Profile
    Vinish Kapoor Enlightened
    2020-11-02T17:12:42+05:30Added an answer on November 2, 2020 at 5:12 pm

    If the return value of P3_STATUSALL is Approve All then you should change the if condition accordingly. Below is the code:

    if :px_status_lov = 'Approve All' then
    Update table1
    set status = 'APPROVED'
    Where yourconditionColumn = :px_column;
    
    Delete from table1
    where yourconditionColumn = :px_column;
    
    apex_util.set_session_state('P3_STATUSM', 'Approved');
    
    elsif :px_status_lov = 'Reject All' then
    
    Update table1
    set status = 'REJECTED'
    Where yourconditionColumn = :px_column;
    
    apex_util.set_session_state('P3_STATUSM', 'Rejected');
    end if;

    Items to submit: P3_STATUSALL,P3_PID

    Items to return: P3_STATUSM

    • 0
    • Share
      Share
      • Share on Facebook
      • Share on Twitter
    • Swarup

      Swarup

      • India
      • 30 Questions
      • 119 Answers
      • 0 Best Answers
      • 202 Points
      View Profile
      Swarup Professional
      2020-11-02T17:25:23+05:30Replied to answer on November 2, 2020 at 5:25 pm
      This answer was edited.

       

      Same as previous not firing

       

      apex_util.set_session_state('P3_STATUSM', 'Approved');  this is not working but the status field is changing 
      • 0
      • Share
        Share
        • Share on Facebook
        • Share on Twitter
  3. Vinish Kapoor

    Vinish Kapoor

    • 1 Question
    • 727 Answers
    • 63 Best Answers
    • 4k Points
    View Profile
    Vinish Kapoor Enlightened
    2020-11-02T17:31:01+05:30Added an answer on November 2, 2020 at 5:31 pm

    What event did you choose for the DA for P3_STATUSALL?

    Is the report region is being refreshed after selecting the value from P3_STATUSALL?

    • 0
    • Share
      Share
      • Share on Facebook
      • Share on Twitter
    • Swarup

      Swarup

      • India
      • 30 Questions
      • 119 Answers
      • 0 Best Answers
      • 202 Points
      View Profile
      Swarup Professional
      2020-11-02T17:35:42+05:30Replied to answer on November 2, 2020 at 5:35 pm

      Yes Refresh->Region

      • 0
      • Share
        Share
        • Share on Facebook
        • Share on Twitter
      • Vinish Kapoor

        Vinish Kapoor

        • 1 Question
        • 727 Answers
        • 63 Best Answers
        • 4k Points
        View Profile
        Vinish Kapoor Enlightened
        2020-11-02T17:37:47+05:30Replied to answer on November 2, 2020 at 5:37 pm

        Is it refreshing the report?

        • 0
        • Share
          Share
          • Share on Facebook
          • Share on Twitter
        • Swarup

          Swarup

          • India
          • 30 Questions
          • 119 Answers
          • 0 Best Answers
          • 202 Points
          View Profile
          Swarup Professional
          2020-11-02T17:39:16+05:30Replied to answer on November 2, 2020 at 5:39 pm

          yes it is and am not getting P3_Statusm Valued as Approved in front end but in db it is getting stored

          • 0
          • Share
            Share
            • Share on Facebook
            • Share on Twitter
  4. Vinish Kapoor

    Vinish Kapoor

    • 1 Question
    • 727 Answers
    • 63 Best Answers
    • 4k Points
    View Profile
    Vinish Kapoor Enlightened
    2020-11-02T17:41:33+05:30Added an answer on November 2, 2020 at 5:41 pm

    It means the dynamic action on the list change event is firing. Because it refreshes the page.

    So you have to check the values returned by the STATUSM and STATUSALL lists. Also, check and update and delete statement if you specified the correct condition.

    • 0
    • Share
      Share
      • Share on Facebook
      • Share on Twitter
    • Swarup

      Swarup

      • India
      • 30 Questions
      • 119 Answers
      • 0 Best Answers
      • 202 Points
      View Profile
      Swarup Professional
      2020-11-02T17:59:49+05:30Replied to answer on November 2, 2020 at 5:59 pm

      if :p3_statusall = 'Approve All' then
      Update times
      set statusm = case when statusm is null then 'APPROVED' else statusm end;
      set statust = case when statust is null then 'APPROVED' else statust end;

      Where pid = :p3_pid;
      apex_util.set_session_state('P3_STATUSM', 'Approved');

      elsif :p3_statusall = 'Reject All' then
      Update times
      set statusm = case when statusm is null then 'REJECTED' else statusm end;
      set statust = case when statust is null then 'REJECTED' else statust end;
      Where pid = :p3_pid;
      apex_util.set_session_state('P3_STATUSM', 'Rejected');

      end if;

       

       

      i have multiple fileds to be set approve i.e., 5 days a week(statusm,statust,statusw ...) will this plsql work

      • 0
      • Share
        Share
        • Share on Facebook
        • Share on Twitter
      • Swarup

        Swarup

        • India
        • 30 Questions
        • 119 Answers
        • 0 Best Answers
        • 202 Points
        View Profile
        Swarup Professional
        2020-11-02T18:56:56+05:30Replied to answer on November 2, 2020 at 6:56 pm

        When i am trying to do multiple row update i am getting folowing error

        • 0
        • Share
          Share
          • Share on Facebook
          • Share on Twitter
        • Vinish Kapoor

          Vinish Kapoor

          • 1 Question
          • 727 Answers
          • 63 Best Answers
          • 4k Points
          View Profile
          Best Answer
          Vinish Kapoor Enlightened
          2020-11-02T20:18:00+05:30Replied to answer on November 2, 2020 at 8:18 pm

          Delete the True action to Refresh the region. Instead, create another True action to execute JavaScript code and add the following code in it:

          location.reload();
          • 0
          • Share
            Share
            • Share on Facebook
            • Share on Twitter
          • Swarup

            Swarup

            • India
            • 30 Questions
            • 119 Answers
            • 0 Best Answers
            • 202 Points
            View Profile
            Swarup Professional
            2020-11-02T20:31:31+05:30Replied to answer on November 2, 2020 at 8:31 pm

            Thanx so much sir for your kind support

            • 0
            • Share
              Share
              • Share on Facebook
              • Share on Twitter

You must login to add an answer.

Forgot Password?

Sidebar

Explore

  • Ask Question
  • Add Post

Creative Commons License
This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.
© 2022 OrclQA.Com. All Rights Reserved.
Privacy Policy - About Us