12 Replies Latest reply: Dec 28, 2012 10:03 AM by user517360 RSS

    Why a custom process run multiple times

    user517360
      Hi,

      I have a page tied to 2 tables namely inv_header and inv_detail. I have a tabular form in the detail and also has a button call "delete checked items". When i press the button, it will delete the record from the inv_detail base on the row being selected. At the same time, i will update the inventory of the selected products and add the inventory back to the t_product tables. I have got all these working and the following is my code .... (Of course part of the code is after looking at Denes Kubicek's site)

      what i do not understand is if i have 2 items checked, this process run twice. If i checked 4 items, it will run 4 times. Why is that so? I am using APEX 4.2

      DECLARE
      vrow BINARY_INTEGER;
      BEGIN
      FOR i IN 1 .. apex_application.g_f01.COUNT
      LOOP
      vrow := apex_application.g_f01 (i);

      UPDATE t_product
      SET PROD_INVENTORY = PROD_INVENTORY + apex_application.g_f05 (vrow)
      WHERE PRODUCT_CODE = apex_application.g_f04 (vrow);
      END LOOP;
      END;

      Thanks a million
      Alvin
        • 1. Re: Why a custom process run multiple times
          Prabodh
          Welcome to Oracle Forums!
          Please acquaint yourself with the FAQ and forum etiquette if you haven't already done so.

          Always state
          <ul>
          <li>Apex Version</li>
          <li>DB Version and edition</li>
          <li>Web server used.I.e. EPG, OHS, ApexListner Standalone or with J2EE container</li>
          <li>When asking about forms always state tabular form if it is a tabular form</li>
          <li>When asking about reports always state Classic / IR</li>
          <li>Always post code snippets enclosed in a pair of &#123;code&#125; tags as explained in FAQ</li>
          </ul>

          >
          I have a page tied to 2 tables namely inv_header and inv_detail. I have a tabular form in the detail and also has a button call "delete checked items". When i press the button, it will delete the record from the inv_detail base on the row being selected. At the same time, i will update the inventory of the selected products and add the inventory back to the t_product tables. I have got all these working and the following is my code .... (Of course part of the code is after looking at Denes Kubicek's site)

          what i do not understand is if i have 2 items checked, this process run twice. If i checked 4 items, it will run 4 times. Why is that so? I am using APEX 4.2
          DECLARE
          vrow BINARY_INTEGER;
          BEGIN
          FOR i IN 1 .. apex_application.g_f01.COUNT
          LOOP
          vrow := apex_application.g_f01 (i);
          
          UPDATE t_product
          SET PROD_INVENTORY = PROD_INVENTORY + apex_application.g_f05 (vrow)
          WHERE PRODUCT_CODE = apex_application.g_f04 (vrow);
          END LOOP;
          END;
          What are f01 , f04 ,etc.? Post your report query and identify checkbox column.



          Cheers,
          • 2. Re: Why a custom process run multiple times
            user517360
            Hi,

            Thanks for the reply.

            Apex Version - 4.2
            DB Version and edition - Oracle Database Express
            Web server used.I.e. EPG, OHS, ApexListner Standalone or with J2EE container - Apex Listener Standalone

            The page consists of the header which is just with normal region with page items displaying the invoice header information while the detail items, i use a tabular form to allow the users to manipulate the data for the detail items. I have a button called "checked delete items" which will delete the selected items in the tabular form. Before the multiple row delete process kick in, i have to run another PLSQL process to add the product qty back to my product table. Below is the code snippet.
            DECLARE
            vrow BINARY_INTEGER;
            BEGIN
            FOR i IN 1 .. apex_application.g_f01.COUNT
            LOOP
            vrow := apex_application.g_f01 (i);
             
            UPDATE t_product
            SET PROD_INVENTORY = PROD_INVENTORY + apex_application.g_f05 (vrow)
            WHERE PRODUCT_CODE = apex_application.g_f04 (vrow);
            END LOOP;
            END;
            Everything runs fine except that this piece of code got executed number of n times depends on how many item checked. If there are 2 items checked at the tabular form, this code will run twice and hence my product table qty got updated twice. If there are 3 items checked at the tabular form, then this code will be executed 3 times. I do not understand why.

            apex_application.g_f04 is referencing to the tabular form which hold the item product code and apex_application.g_f05 is the qty sold for that particular invoice.

            Thanks
            Alvin
            • 3. Re: Why a custom process run multiple times
              Prabodh
              You still have not explained what is g_f01, the item you are iterating on.
              If that is checkbox, then what is the value ? is it an unique id or pk of the rows?
              Checkbox behavior is a little different as explained here http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35127/apex_app.htm#CHDICJDA
              Cheers.
              • 4. Re: Why a custom process run multiple times
                user517360
                Hi,

                Thanks for the prompt reply, my sincere apologies for not being clear. I have read that and i understood the checkbox behavior.

                g_f01 reference to a checkbox and it is NOT referencing any unique id or pk. I have examined the output, the array detect 2 rows being selected if there are 2 rows being checked. Just that the process execute twice if i have 2 rows selected and it execute 3 times if i have 3 rows selected.

                Thanks
                Alvin
                • 5. Re: Why a custom process run multiple times
                  Prabodh
                  Hi Alvin,
                  Thanks for the prompt reply, my sincere apologies for not being clear. I have read that and i understood the checkbox behavior.
                  If this is the case then you know that the "value" of the checkbox needs to have some correlation with the row number, PK or some unique identifier for the columns in the row.
                  >
                  g_f01 reference to a checkbox and it is NOT referencing any unique id or pk. I have examined the output, the array detect 2 rows being selected if there are 2 rows being checked. Just that the process execute twice if i have 2 rows selected and it execute 3 times if i have 3 rows selected.
                  >

                  In your code you have
                  vrow := apex_application.g_f01 (i);
                  and then you use the vorw like this
                  UPDATE t_product
                  SET PROD_INVENTORY = PROD_INVENTORY + apex_application.g_f05 (vrow)
                  WHERE PRODUCT_CODE = apex_application.g_f04 (vrow);
                  From this code it appears that g_f04 is a UK/PK to update one and only one row of t_product table. But it is not clear how g_f04(vrow) gets you the correct key value.

                  What is the value that you get in vrow? Is that the right mechanism to get the corresponding g_f04 and g_f05 from the selected row? This perhaps has something to do with why the process runs multiple times.

                  That is the most I can contribute without knowing more about g_f01 , how it is constructed and what values it has.
                  You can use Firefox/Firebug to inspect the values of checkbox and figure it out for yourself.
                  Cheers,
                  • 6. Re: Why a custom process run multiple times
                    user517360
                    Hi Prabodh,

                    Thanks a lot for replying and being so patience with me .... Thanks in advance.

                    My problem form involve 2 tables, inv_dtl and t_product. When my users issue an invoice, i will deduct the stock from t_product and when they delete a record from the invoice, i need to add the qty of the product deleted back to the inventory.

                    The tabular form is tied to the inv_dtl and below is the ddl of the table
                      CREATE TABLE "BACKOFFICE"."INV_DTL" 
                       (     "INV_DTL_NO" NUMBER, 
                         "INV_DTL_INV_HD_NO" VARCHAR2(16 BYTE), 
                         "INV_DTL_PROD_CODE" VARCHAR2(24 BYTE), 
                         "INV_DTL_QTY" NUMBER(8,0), 
                         "INV_DTL_DISC_PCT" NUMBER(8,2), 
                         "INV_DTL_AMT" NUMBER(8,2), 
                         "INV_DTL_DISC_AMT" NUMBER(8,2), 
                         "INV_DTL_UNIT_PRICE" NUMBER(8,2), 
                          CONSTRAINT "INV_DTL_PK" PRIMARY KEY ("INV_DTL_NO")
                    The inv_dtl no is generated by a sequence.

                    The tabular form is created by the wizard. I just use it as is. My problem now is the g_f01 is tied to the row selector. I have examine the value, it return the row number in the tabular form. So how do i co-relate this row selector to my primary key?

                    eg: if i have four rows in the inv_dtl, then g_f01 will return 1,2,3,4 respectively to the row. The apex_application.g_f01.COUNT return the number of row being checked and i have verified it, when i have 2 rows selected, apex_application.g_f01.COUNT return 2.

                    I have a button called "delete checked items". Upon users selected the record and press this button, it will trigger the ApplyMRD process. But before that, i execute a custom process to put the inventory back to t_product and this process invoke this PLSQL code which u have seen
                     -- this is the simplified version, the logic is more complex than this. But the rest is not important at the moment
                    DECLARE
                       vrow   BINARY_INTEGER;
                    BEGIN
                       FOR i IN 1 .. apex_application.g_f01.COUNT
                       LOOP
                          vrow := apex_application.g_f01 (i);
                    
                          UPDATE t_product
                             SET PROD_INVENTORY = PROD_INVENTORY + apex_application.g_f05 (vrow)
                           WHERE PRODUCT_CODE = apex_application.g_f04 (vrow);
                       END LOOP;
                    END;
                    I have also examine the value for both apex_application.g_f05 and apex_application.g_f04. It tied to the correct value which is the qty of the inv_dtl and product_code respectively. The vrow tell which row number it should pick

                    Eg -- in tabular form
                    Product Code Qty
                    A111 2
                    B222 3
                    C333 5
                    D444 10

                    If row 2 and 4 is checked, the apex_application.g_f05 (grow) return 2 and 10 respectively and apex_application.g_f04 return B222 and D444 respectively which is doing fine till now. But when i examine my T_Product table, the inventory being add twice as the process run twice which i do not understand why ....

                    Example in t_product the current inventory for
                    B222 is 10
                    D444 is 5

                    After the process, it becomes 16 and 25 respectively.

                    If i have only one row being selected in the tabular form, it only update one product back to T_PRODUCT table ... Which is fine.

                    Thanks for the help thus far ....
                    Alvin
                    • 7. Re: Why a custom process run multiple times
                      Prabodh
                      Hi,

                      When you Edit this process what do you see in Process Point ? Do you see Tabular Form: Tabular Forms ?
                      If yes, that is your problem. Tabular Form processes execute once for each selected row.
                      If your process is Tabular Form: Tabular Form then you will need to delete it and recreate it without selecting the Tabular Form on the first page of the Create Process Wizard.

                      Cheers,
                      • 8. Re: Why a custom process run multiple times
                        user517360
                        Hi Prabodh,

                        You HAVE ABSOLUTELY SAVE MY DAY !!!!!!!!!!!!!!!!!! ..... Yes .... that's the problem ..... Now it updated correctly.

                        Thanks a million ..... !!!

                        ALvin
                        • 9. Re: Why a custom process run multiple times
                          user517360
                          Hi Prabodh,

                          I have one more question here .... hope u don mind i continue using this thread.

                          This is referring to the same tabular form we have been discussing. I have an addrow button which, well add new row to the tabular form. As u are aware, whenever i save the newly inserted record. I need to deduct stock from T_PRODUCT table. My question is how do i detect the new row and then update the product table accordingly?

                          Example

                          INV_DETAIL

                          Product Code Qty
                          A111 2 < Stock deducted
                          B111 2 < Stock deducted
                          C111 2 < Stock deducted

                          New Row
                          D111 2 <need to deduct stock upon pressing "apply changes" button

                          I have read some of the posting. It seems the only way is to use java script gNewrow global variable?

                          Thanks again

                          Alvin

                          Edited by: user517360 on Dec 28, 2012 2:08 AM
                          • 10. Re: Why a custom process run multiple times
                            Prabodh
                            Hi Alvin,

                            I have not really used this in any of my applications, but this might be useful.
                            The apex_application.g_frowid item is null for new rows. So, perhaps you can use that in your logic to find new rows. This will need to be a separate block or process with no reference to the checkbox(f01).

                            Something like
                            FOR i IN 1 .. apex_application.g_frowid.COUNT
                            LOOP
                             if apex_application.g_frowid(i) is null then
                               UPDATE t_product
                                SET PROD_INVENTORY = PROD_INVENTORY - apex_application.g_f05 (i)  
                                WHERE PRODUCT_CODE = apex_application.g_f04 (i);
                             end if;
                            END LOOP;
                            Sorry, I have not tested this snippet but will do so when I get a chance to do so.

                            Cheers,
                            • 11. Re: Why a custom process run multiple times
                              user517360
                              Hi Prabodh,

                              Thanks for the prompt reply. I will try it out.

                              Thanks
                              Alvin
                              • 12. Re: Why a custom process run multiple times
                                user517360
                                Hi Prabodh,

                                The snippet work perfectly fine .... !!! Thanks again ....

                                Alvin