This discussion is archived
12 Replies Latest reply: Dec 28, 2012 8:03 AM by user517360 RSS

Why a custom process run multiple times

user517360 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hi Prabodh,

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

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

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

    Alvin

Legend

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