1 2 Previous Next 22 Replies Latest reply on Nov 26, 2018 4:39 PM by Chitrasai1-Oracle

    How to reference the column of an interactive grid while using in a dynamic action

    Srini-S

      I have an interactive grid in which I plan to make one column editable and the user entered value is what I plan to send to EBusiness suite through an API using PL/SQL. How can I reference the column in the pl/sql code? For page items I use :Item and it works. For  interactive grid, how does it work? Also, how does it process if there is more than one row? Execute condition 'For Each Row' processes the data for each row?

      What is the function of Save button in interactive grid ? I assume it is to be used only if the grid is based on a table , and that can be saved just by updating values then and there itself as entered in the grid instead of any logics or manipulations with any other items or applications?

        • 1. Re: How to reference the column of an interactive grid while using in a dynamic action
          Pierre Yotti

          Srini-S wrote:

           

          I have an interactive grid in which I plan to make one column editable and the user entered value is what I plan to send to EBusiness suite through an API using PL/SQL.

          Which API. What is that API for? Save the IG Model Data?

          How can I reference the column in the pl/sql code?

          Which PL/SQL Code?

          For page items I use :Item and it works.

          Sure it should works.

          For  interactive grid, how does it work?

          IG is process using built in Interactive Grid - Automatic Row Processing (DML)

          Bildschirmfoto 2018-11-24 um 20.06.34.png

           

          or you can manually process Interactive Grid data using PL/SQL like this

           

          begin  
               case :APEX$ROW_STATUS  
               when 'I' then -- Note: In EA2 this has been changed from I to C for consistency with Tabular Forms  
                   insert into emp ( empno, ename, deptno )  
                   values ( :EMPNO, :ENAME, :DEPTNO )  
                   returning rowid into :ROWID;  
               when 'U' then  
                   update emp  
                      set ename  = :ENAME,  
                          deptno = :DEPTNO  
                    where rowid  = :ROWID;  
               when 'D' then  
                   delete emp  
                   where rowid = :ROWID;  
               end case;  
          end;  
          

           

          Also, how does it process if there is more than one row?

           

           

          Bildschirmfoto 2018-11-24 um 20.03.44.png

           

           

           

          What is the function of Save button in interactive grid ?

          look the above answers

          • 2. Re: How to reference the column of an interactive grid while using in a dynamic action
            Srini-S

            Thanks. I use a PL/SQL code something like below.

             

            DECLARE

            BEGIN

            apps.xx_apl_apex_pkg.charge_time_prc(:P3_WORK_ORDER,sysdate,:HOURS_TO_CHARGE,:OPERATION_SEQ_NUM,:RESOURCE_SEQ_NUM,:APP_USER,:HOURS_TO_CHARGE,:RES_CODE);

            END;

             

            Here, : P3_WORK_ORDER is a page item . However, other variables like HOURS_TO_CHARGE are columns of interactive grid that are displayed based on a query.

             

            My precise question was :  Is the syntax :HOURS_TO_CHARGE sufficient to process the interactive grid's column value for each selected row?

             

            It seems to be not doing anything with this syntax.

            • 3. Re: How to reference the column of an interactive grid while using in a dynamic action
              Pierre Yotti

              Srini-S wrote:

               

              Thanks. I use a PL/SQL code something like below.

               

              DECLARE

              BEGIN

              apps.xx_apl_apex_pkg.charge_time_prc(:P3_WORK_ORDER,sysdate,:HOURS_TO_CHARGE,:OPERATION_SEQ_NUM,:RESOURCE_SEQ_NUM,:APP_USER,:HOURS_TO_CHARGE,:RES_CODE);

              END;

               

              Here, : P3_WORK_ORDER is a page item . However, other variables like HOURS_TO_CHARGE are columns of interactive grid that are displayed based on a query.

               

              My precise question was :  Is the syntax :HOURS_TO_CHARGE sufficient to process the interactive grid's column value for each selected row?

               

              It seems to be not doing anything with this syntax.

               

              You should use PLSQL Process and something like this to do that

               

              begin    
                   case :APEX$ROW_STATUS    
                   when 'I' then -- Note: In EA2 this has been changed from I to C for consistency with Tabular Forms    
                       insert into emp ( empno, ename, deptno )    
                       values ( :EMPNO, :ENAME, :DEPTNO )    
                       returning rowid into :ROWID;    
                   when 'U' then    
                       update emp    
                          set ename  = :ENAME,    
                              deptno = :DEPTNO    
                        where rowid  = :ROWID;    
                   when 'D' then    
                       delete emp    
                       where rowid = :ROWID;    
                   end case;    
              end;  
              

               

               

              That you can use :HOURS_TO_CHARGE as Column Name

              • 4. Re: How to reference the column of an interactive grid while using in a dynamic action
                Srini-S

                My PLSQL code is similar to this except that I am not checking any stauses of the  row, but processing all the rows (for test only one row though). That PLSQL that I gave in previous post is not inserting any data (not processing ).

                • 5. Re: How to reference the column of an interactive grid while using in a dynamic action
                  Pierre Yotti

                  Srini-S wrote:

                   

                  My PLSQL code is similar to this except that I am not checking any stauses of the  row, but processing all the rows (for test only one row though). That PLSQL that I gave in previous post is not inserting any data (not processing ).

                   

                  You should do it like in the screenshot

                  Bildschirmfoto 2018-11-24 um 20.39.05.png

                   

                   

                  declare
                  -------
                   begin
                      case
                          :apex$row_status
                          when 'C' then -- C means when new Rows are created
                              apps.xx_apl_apex_pkg.charge_time_prc(
                                  :p3_work_order,
                                  sysdate,
                                  :hours_to_charge,
                                  :operation_seq_num,
                                  :resource_seq_num,
                                  :app_user,
                                  :hours_to_charge,
                                  :res_code
                              );
                          when 'U' then   --- Here is just for update purpose   
                              update emp
                                 set ename =:ename,
                                     deptno =:deptno
                               where rowid =:rowid;
                  
                  
                          when 'D' then   -- And when you want to delete   
                              delete emp
                               where rowid =:rowid;
                  
                  
                      end case;
                  end;
                  

                   

                   

                  When you click the Save Button for the IG Grid it will run the above code based of the condition if it is just a update(insert/delete. It will not works with DA. It works with PL/SQL Process like above.

                   

                  You may also take a look here

                  APEX 5.1 How to manually process Interactive Grid data using PL/SQL

                  • 6. Re: How to reference the column of an interactive grid while using in a dynamic action
                    Srini-S

                    Basically, here the conditions to look for the row status is that in this case, you dont want to process all rows. In my case, I want to process all rows and so I am not even looking at the row status column as a condition. The Hours charged column is what I am passing to the PLSQL but it is passing just null value which is defaulted when first queried. I am not saving the data in the same table as it was queried from. I use the 'process' , click on save button, which calls the 'process'. Within the process, I have the PLSQL code that I gave. Issue seems to be that the :HOURS_CHARGED which is column identifier, is not having any value when passed to the plsql code. That needs to be addressed, unless you confirm that the row status column has to be used in the plsql code for successful processing.

                     

                    I can confirm that the plsql code is being executed since I find the new record in the destination system being called, but value as null. I am picking two columns values from this grid to pass to that pl/sql code (:HOURS_TO_CHARGE and :OPERATION_SEQ_NUM ). Both are being passed as null.

                     

                    DECLARE

                    BEGIN

                    apps.xxx_apl_apex_pkg.charge_time_prc(x,y,:P3_WORK_ORDER,sysdate,:HOURS_TO_CHARGE,:OPERATION_SEQ_NUM,:APP_USER,:HOURS_TO_CHARGE);

                    END;

                    • 7. Re: How to reference the column of an interactive grid while using in a dynamic action
                      Pierre Yotti

                      Srini-S wrote:

                       

                      Basically, here the conditions to look for the row status is that in this case, you dont want to process all rows. In my case, I want to process all rows and so I am not even looking at the row status column as a condition. The Hours charged column is what I am passing to the PLSQL but it is passing just null value which is defaulted when first queried. I am not saving the data in the same table as it was queried from. I use the 'process' , click on save button, which calls the 'process'. Within the process, I have the PLSQL code that I gave. Issue seems to be that the :HOURS_CHARGED which is column identifier, is not having any value when passed to the plsql code. That needs to be addressed, unless you confirm that the row status column has to be used in the plsql code for successful processing.

                       

                      I can confirm that the plsql code is being executed since I find the new record in the destination system being called, but value as null. I am picking two columns values from this grid to pass to that pl/sql code (:HOURS_TO_CHARGE and :OPERATION_SEQ_NUM ). Both are being passed as null.

                       

                      DECLARE

                      BEGIN

                      apps.xxx_apl_apex_pkg.charge_time_prc(x,y,:P3_WORK_ORDER,sysdate,:HOURS_TO_CHARGE,:OPERATION_SEQ_NUM,:APP_USER,:HOURS_TO_CHARGE);

                      END;

                       

                      As i said you should follow the above Answers. It will not works as you said. Just try it and let us know  if it does not works.

                      • 8. Re: How to reference the column of an interactive grid while using in a dynamic action
                        Srini-S

                        Adding the row condition to the code did not help. It is processing this plsql code, but inserts null data wherever the grid's column is referenced.

                         

                        DECLARE

                        BEGIN

                        case

                        :apex$row_status

                        when 'U' then

                        apps.xxxx_apl_apex_pkg.charge_time_prc(P3_WORK_ORDER,sysdate,:HRS_TO_CHARGE,:OP_SEQ,:RES_SEQ,:APP_USER,:HOURS_TO_CHARGE,:RES_CODE);

                        end case;

                        END;

                        • 9. Re: How to reference the column of an interactive grid while using in a dynamic action
                          Pierre Yotti

                          Where did you put that code ? Please post a screenshot ?

                          • 10. Re: How to reference the column of an interactive grid while using in a dynamic action
                            Srini-S

                            Deleting the screenshot as it is seen and understood.

                            • 11. Re: How to reference the column of an interactive grid while using in a dynamic action
                              Pierre Yotti

                              Srini wrote:

                               

                              DECLARE

                              BEGIN

                              case

                              :apex$row_status

                              when 'U' then

                              apps.xxxx_apl_apex_pkg.charge_time_prc(P3_WORK_ORDER,sysdate,:HRS_TO_CHARGE,:OP_SEQ,:RES_SEQ,:APP_USER,:HOURS_TO_CHARGE,:RES_CODE);

                              end case;

                              END;

                               

                              "U" is for update, for news rows you should use "C"

                               

                              Did you put the IG Region here?

                               

                              Bildschirmfoto 2018-11-24 um 21.20.55.png

                               

                               

                              And what did you set here?

                               

                              Bildschirmfoto 2018-11-24 um 21.21.13.png

                              • 13. Re: How to reference the column of an interactive grid while using in a dynamic action
                                Pierre Yotti

                                Srini-S wrote:

                                1. Yes please.

                                2. All submitted rows.

                                 

                                It should works. I am not Idea why is not working for you. Can you expose your Local Development Environment to the outside world with Ngrok?

                                I also did it and it works for me

                                https://2b6b876a.ngrok.io/ords/f?p=104

                                 

                                Can you do it and share workspace, username and password?

                                • 14. Re: How to reference the column of an interactive grid while using in a dynamic action
                                  Srini-S

                                  "U" is for update, for news rows you should use "C"

                                   

                                  I am updating the only record fetched and calling pl/sql code. I guess it should be U then?

                                  1 2 Previous Next