4 Replies Latest reply: Dec 7, 2012 2:39 PM by Srikkanth.M RSS

    How to update field when check box checked

    Srikkanth.M
      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
          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
            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
              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
                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