This discussion is archived
4 Replies Latest reply: Dec 7, 2012 12:39 PM by Srikkanth.M RSS

How to update field when check box checked

Srikkanth.M Pro
Currently Being Moderated
Hi All,

I am using oracle Forms 10g,

I have a block with 20 fields.So first 10 Fields are not updatable and remaining are Editable. So the user will enter the Data and changes the data.And check the Check box and save the data.

Inside the save button i have written the update code.

When the user Edit two or three records and click three Check box and click the save button only one record is getting entred in my custom table.

This is my code please correct me where i went worng.

This is my code inside my button

declare
Begin
If :ASSEMBLIES_BLOCK.CHECK_IN_OUT='Y' THEN
IF SHOW_ALERT ('UPDATE_ALERT') = alert_button1 THEN
LOOP
XXTLX_PO_LOGOSTICS.XXTLX_PO_LOGOSTICS_UPDATE;
EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE';
NEXT_RECORD;
END LOOP;
STANDARD.COMMIT;
fnd_message.set_string ('Transaction Saved Successfully');
fnd_message.show;
ELse
NULL;
END If;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
NULL;
END;


I have write a procedure in program unit.

XXTLX_PO_LOGOSTICS.XXTLX_PO_LOGOSTICS_UPDATE

Assemblies block is my current block that user enter the data and click the check box and save the data.

     Update XXTLX_PO_ASSEMBLIES set CASE_NO=nvl(:assemblies_block.CASE_NO,Null),
          PACKAGE_TYPE=nvl(:assemblies_block.PACKAGE_TYPE,null),
          PACKAGE_QTY=nvl(:assemblies_block.PACKAGE_QTY,Null),
          GROSS_WT=nvl(:assemblies_block.GROSS_WEIGHT,Null),
          OPI_DATE=nvl(:assemblies_block.OPI_DATE,Null)               
          Where ponum=:DETAIL_BLOCK.PO_NUMBER
          AND PO_LINE_NUM=:DETAIL_BLOCK.LINE_NUM          
          AND BOM_NO=:assemblies_block.BOM_NO;




So when we click the multiple check box and save the data only one data is entering.

Thanks & Regards
Srikkanth
  • 1. Re: How to update field when check box checked
    MLBrown Journeyer
    Currently Being Moderated
    According to this portion of your code it looks like you want to loop through the records and do some updates on them:
    .
    .
    LOOP
      XXTLX_PO_LOGOSTICS.XXTLX_PO_LOGOSTICS_UPDATE;
      EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE';
      NEXT_RECORD;
    END LOOP;
    
    STANDARD.COMMIT;
    fnd_message.set_string ('Transaction Saved Successfully');
    fnd_message.show;
    .
    .
    However, if you are currently sitting on the third record you will miss the first two records.

    You should really navigate back to the first record in the block then loop through them. Something like:
    declare
    Begin
      If :ASSEMBLIES_BLOCK.CHECK_IN_OUT = 'Y' THEN
        IF SHOW_ALERT ('UPDATE_ALERT') = alert_button1 THEN
          first_record;  -- Go to the first record in the block before looping  <-- I ADDED THIS LINE
          LOOP
            XXTLX_PO_LOGOSTICS.XXTLX_PO_LOGOSTICS_UPDATE;
            EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE';
            NEXT_RECORD;
          END LOOP;
    
          STANDARD.COMMIT;
          fnd_message.set_string ('Transaction Saved Successfully');
          fnd_message.show;
        ELse
          NULL;
        END If;
      END IF;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        NULL;
      WHEN OTHERS THEN
        NULL;
    END;
  • 2. Re: How to update field when check box checked
    Srikkanth.M Pro
    Currently Being Moderated
    Hi Mr. Brown,

    Thanks for your quick response.

    One small question, my requirement is only the checked fields need to get updated. If in a block there are 10 records means ill update only the 3rd and 4th record and then click the check box.. and then ill click the save button.

    My doubt is in the update statement whether i need to point out the currently checked records.

    Can you please clarify this, and ill check the code provide.

    Thanks & Regards
    Srikkanth
  • 3. Re: How to update field when check box checked
    MLBrown Journeyer
    Currently Being Moderated
    I guess I don't understand.

    >
    ... my requirement is only the checked fields need to get updated. If in a block there are 10 records means ill update only the 3rd and 4th record and then click the check box.. and then ill click the save button.
    >

    Is this block a database block or a non-database block? If it is a database block then Oracle can handle updates to that block for you because if keeps tabs if a record was updated or not and you wouldn't need to do an UPDATE statement.

    If it is a non-database block, and you are looping through each record and only updating the items that have checkboxes next to them you would still have to go to the first record then step through each record and see if it needs to be updated. I am just guessing here because I don't think I have a good grasp on what you are doing, but you could change your XXTLX_PO_LOGOSTICS.XXTLX_PO_LOGISTICS_UPDATE package to only update the items that have checkboxes next to them, by checking each checkbox:
    -- Check Case No
    if nvl(:assemblies_block.chase_no_check,'N') = 'Y' then
      update xxtlx_po_assemblies
         set case_no = :assemblies_block.case_no
       where ponum = :detail_block.po_number
         and po_line_num = :detail_block.line_num
         and bom_no = :assemblies_block.bom_no;
    end if;
    
    -- Check Package Type
    if nvl(:assemblies_block.package_type_check,'N') = 'Y' then
      update xxtlx_po_assemblies
         set package_type = :assemblies_block.package_type
       where ponum = :detail_block.po_number
         and po_line_num = :detail_block.line_num
         and bom_no = :assemblies_block.bom_no;
    end if;
    
    -- Check Package Qty
    .
    .
    .
  • 4. Re: How to update field when check box checked
    Srikkanth.M Pro
    Currently Being Moderated
    Hi Mr.Brown,

    You are correct,

    i have used the statement inside my update statement and its works..

    if nvl(:assemblies_block.package_type_check,'N') = 'Y'


    Thanks a lot.

    Thanks & Regards
    Srikkanth.M

Legend

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