4 Replies Latest reply on Jan 3, 2017 3:50 PM by J Reinhart

    FRM-40654: Record has been updated Requery block to see change

    3156248

      Hi All,

       

      We are creating POs by using PR then RFQand PO.

      By using the particular PR requestor autocreate the PO and if we change the any field (Qty, Promised, Needby) in that PO we got below error.

      “FRM-40654: Record has been updated Requery block to see change“. Please find the below attached file for error.

       

      Kindly suggest any workaround to fix this issue.

       

      Thanks,

      J.Balachandra

        • 1. Re: FRM-40654: Record has been updated Requery block to see change
          J Reinhart

          This issue has been seen when there are spaces at the beginning or end of a character field.  The form trims the field, which removes the leading/trailing spaces.  However that means that the data on screen no longer matches what is in the table, so the form thinks someone else has modified the record. 

           

          Example (the quotes here are just to show spaces, imagine they aren't entered on the form)

          Suppose a field reads "Ship asap " (with a space as the last character of the text)

          The form trims that to be "Ship asap" (no space at the end)

          The user changes some unrelated field, such as the need-by date, and tries to save.

          The form finds that "Ship asap" isn't the same as the "Ship asap " that's on the record in the table, so it thinks that someone else must have modified the record.

           

          Use sql to check all the text fields on an affected PO (and its lines, shipments, distributions) for spaces as the last or first character.  In your test instance, try to modify the PO.  You should see the error.  Use sql to remove the leading/trailing spaces from the text field and save that change.  Then try to modify the PO again.  If the error is gone, then it confirms this as the issue. 

           

          Regards,

           

          J

          • 2. Re: FRM-40654: Record has been updated Requery block to see change
            3156248


            Hi Reinhart,

             

            Thanks for your response.

             

             

            1. How can we find where spaces happened?

            2. Please provide the sql code to find the spaces and remove the spaces.

             

            Thanks,

            J.Balachandra

            • 3. Re: FRM-40654: Record has been updated Requery block to see change
              J Reinhart

              Hi J.Balachandra

               

              Comparing the length of the text field with the length of the same field after trimming should identify the columns that have leading or trailing spaces.  For example, if the value is 17 characters long as is, and 16 characters long after trimming, then it has a leading or trailing space.  Since there are a lot of character fields, I would use sql-to-create-sql, like this:

               

              select

               

              'decode( nvl(length( ' || column_name || '),0) - nvl(length(trim( ' || column_name ||

               

              ') ) ,0 ),0,null,' || '''' ||  column_name || ' ' ||  '''' || ') ||'

               

              from   all_tab_columns where table_name = 'PO_HEADERS_ALL' and data_type = 'VARCHAR2'

               

               

              This will give you a list of decode statements that will return the column name if it has leading/trailing spaces and a null value otherwise.  So what you do is to type "select po_header_id, segment1, " then paste in the query results (without column headers), remove the very last || symbols, then type    from po_headers_all where     then past in the query results again, remove the very last || syymbols and add    is not null  

               

              It will look like the sample below.  When you run it, it will return the PO headers and which fields have leading/trailing spaces.  If the list is short, go address the POs listed.  If the list is too long to be real problems, then its possible some fields are handled properly with leading/trailing spaces and others are not.  You can test some sample records to figure out which columns are the issue.

               

              DO THIS for the po_lines_all, po_line_locations_all, and po_distributions_all tables as well.   If you already know its just one of those, you can go straight to that table.


                   

              select po_header_id, segment1,  /* type this, then past the results of the above query */

              nvl(length( ENABLE_ALL_SITES),0) - nvl(length(trim( ENABLE_ALL_SITES) ) ,0 ),0,null,'ENABLE_ALL_SITES ') ||

               

              nvl(length( CREATED_LANGUAGE),0) - nvl(length(trim( CREATED_LANGUAGE) ) ,0 ),0,null,'CREATED_LANGUAGE ') ||

               

              nvl(length( LAST_UPDATED_PROGRAM),0) - nvl(length(trim( LAST_UPDATED_PROGRAM) ) ,0 ),0,null,'LAST_UPDATED_PROGRAM ') ||

               

              nvl(length( OTM_STATUS_CODE),0) - nvl(length(trim( OTM_STATUS_CODE) ) ,0 ),0,null,'OTM_STATUS_CODE ') ||

               

              nvl(length( OTM_RECOVERY_FLAG),0) - nvl(length(trim( OTM_RECOVERY_FLAG) ) ,0 ),0,null,'OTM_RECOVERY_FLAG ') ||

               

              nvl(length( TYPE_LOOKUP_CODE),0) - nvl(length(trim( TYPE_LOOKUP_CODE) ) ,0 ),0,null,'TYPE_LOOKUP_CODE ') ||

               

              nvl(length( GLOBAL_AGREEMENT_FLAG),0) - nvl(length(trim( GLOBAL_AGREEMENT_FLAG) ) ,0 ),0,null,'GLOBAL_AGREEMENT_FLAG ') ||

               

              nvl(length( CONSIGNED_CONSUMPTION_FLAG),0) - nvl(length(trim( CONSIGNED_CONSUMPTION_FLAG) ) ,0 ),0,null,'CONSIGNED_CONSUMPTION_FLAG ') ||

               

              nvl(length( CONSUME_REQ_DEMAND_FLAG),0) - nvl(length(trim( CONSUME_REQ_DEMAND_FLAG) ) ,0 ),0,null,'CONSUME_REQ_DEMAND_FLAG ') ||

               

              nvl(length( CHANGE_REQUESTED_BY),0) - nvl(length(trim( CHANGE_REQUESTED_BY) ) ,0 ),0,null,'CHANGE_REQUESTED_BY ') ||

               

              nvl(length( XML_FLAG),0) - nvl(length(trim( XML_FLAG) ) ,0 ),0,null,'XML_FLAG ') ||

               

              nvl(length( CONTERMS_EXIST_FLAG),0) - nvl(length(trim( CONTERMS_EXIST_FLAG) ) ,0 ),0,null,'CONTERMS_EXIST_FLAG ') ||

               

              nvl(length( ENCUMBRANCE_REQUIRED_FLAG),0) - nvl(length(trim( ENCUMBRANCE_REQUIRED_FLAG) ) ,0 ),0,null,'ENCUMBRANCE_REQUIRED_FLAG ') ||

               

              nvl(length( PENDING_SIGNATURE_FLAG),0) - nvl(length(trim( PENDING_SIGNATURE_FLAG) ) ,0 ),0,null,'PENDING_SIGNATURE_FLAG ') ||

               

              nvl(length( CHANGE_SUMMARY),0) - nvl(length(trim( CHANGE_SUMMARY) ) ,0 ),0,null,'CHANGE_SUMMARY ') ||

               

              nvl(length( DOCUMENT_CREATION_METHOD),0) - nvl(length(trim( DOCUMENT_CREATION_METHOD) ) ,0 ),0,null,'DOCUMENT_CREATION_METHOD ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE_CATEGORY),0) - nvl(length(trim( GLOBAL_ATTRIBUTE_CATEGORY) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE_CATEGORY ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE1),0) - nvl(length(trim( GLOBAL_ATTRIBUTE1) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE1 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE2),0) - nvl(length(trim( GLOBAL_ATTRIBUTE2) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE2 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE3),0) - nvl(length(trim( GLOBAL_ATTRIBUTE3) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE3 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE4),0) - nvl(length(trim( GLOBAL_ATTRIBUTE4) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE4 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE5),0) - nvl(length(trim( GLOBAL_ATTRIBUTE5) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE5 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE6),0) - nvl(length(trim( GLOBAL_ATTRIBUTE6) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE6 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE7),0) - nvl(length(trim( GLOBAL_ATTRIBUTE7) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE7 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE8),0) - nvl(length(trim( GLOBAL_ATTRIBUTE8) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE8 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE9),0) - nvl(length(trim( GLOBAL_ATTRIBUTE9) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE9 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE10),0) - nvl(length(trim( GLOBAL_ATTRIBUTE10) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE10 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE11),0) - nvl(length(trim( GLOBAL_ATTRIBUTE11) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE11 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE12),0) - nvl(length(trim( GLOBAL_ATTRIBUTE12) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE12 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE13),0) - nvl(length(trim( GLOBAL_ATTRIBUTE13) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE13 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE14),0) - nvl(length(trim( GLOBAL_ATTRIBUTE14) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE14 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE15),0) - nvl(length(trim( GLOBAL_ATTRIBUTE15) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE15 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE16),0) - nvl(length(trim( GLOBAL_ATTRIBUTE16) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE16 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE17),0) - nvl(length(trim( GLOBAL_ATTRIBUTE17) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE17 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE18),0) - nvl(length(trim( GLOBAL_ATTRIBUTE18) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE18 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE19),0) - nvl(length(trim( GLOBAL_ATTRIBUTE19) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE19 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE20),0) - nvl(length(trim( GLOBAL_ATTRIBUTE20) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE20 ') ||

               

              nvl(length( INTERFACE_SOURCE_CODE),0) - nvl(length(trim( INTERFACE_SOURCE_CODE) ) ,0 ),0,null,'INTERFACE_SOURCE_CODE ') ||

               

              nvl(length( REFERENCE_NUM),0) - nvl(length(trim( REFERENCE_NUM) ) ,0 ),0,null,'REFERENCE_NUM ') ||

               

              nvl(length( WF_ITEM_TYPE),0) - nvl(length(trim( WF_ITEM_TYPE) ) ,0 ),0,null,'WF_ITEM_TYPE ') ||

               

              nvl(length( WF_ITEM_KEY),0) - nvl(length(trim( WF_ITEM_KEY) ) ,0 ),0,null,'WF_ITEM_KEY ') ||

               

              nvl(length( MRC_RATE_TYPE),0) - nvl(length(trim( MRC_RATE_TYPE) ) ,0 ),0,null,'MRC_RATE_TYPE ') ||

               

              nvl(length( MRC_RATE_DATE),0) - nvl(length(trim( MRC_RATE_DATE) ) ,0 ),0,null,'MRC_RATE_DATE ') ||

               

              nvl(length( MRC_RATE),0) - nvl(length(trim( MRC_RATE) ) ,0 ),0,null,'MRC_RATE ') ||

               

              nvl(length( PAY_ON_CODE),0) - nvl(length(trim( PAY_ON_CODE) ) ,0 ),0,null,'PAY_ON_CODE ') ||

               

              nvl(length( SEGMENT1),0) - nvl(length(trim( SEGMENT1) ) ,0 ),0,null,'SEGMENT1 ') ||

               

              nvl(length( SUMMARY_FLAG),0) - nvl(length(trim( SUMMARY_FLAG) ) ,0 ),0,null,'SUMMARY_FLAG ') ||

               

              nvl(length( ENABLED_FLAG),0) - nvl(length(trim( ENABLED_FLAG) ) ,0 ),0,null,'ENABLED_FLAG ') ||

               

              nvl(length( SEGMENT2),0) - nvl(length(trim( SEGMENT2) ) ,0 ),0,null,'SEGMENT2 ') ||

               

              nvl(length( SEGMENT3),0) - nvl(length(trim( SEGMENT3) ) ,0 ),0,null,'SEGMENT3 ') ||

               

              nvl(length( SEGMENT4),0) - nvl(length(trim( SEGMENT4) ) ,0 ),0,null,'SEGMENT4 ') ||

               

              nvl(length( SEGMENT5),0) - nvl(length(trim( SEGMENT5) ) ,0 ),0,null,'SEGMENT5 ') ||

               

              nvl(length( SHIP_VIA_LOOKUP_CODE),0) - nvl(length(trim( SHIP_VIA_LOOKUP_CODE) ) ,0 ),0,null,'SHIP_VIA_LOOKUP_CODE ') ||

               

              nvl(length( FOB_LOOKUP_CODE),0) - nvl(length(trim( FOB_LOOKUP_CODE) ) ,0 ),0,null,'FOB_LOOKUP_CODE ') ||

               

              nvl(length( FREIGHT_TERMS_LOOKUP_CODE),0) - nvl(length(trim( FREIGHT_TERMS_LOOKUP_CODE) ) ,0 ),0,null,'FREIGHT_TERMS_LOOKUP_CODE ') ||

               

              nvl(length( STATUS_LOOKUP_CODE),0) - nvl(length(trim( STATUS_LOOKUP_CODE) ) ,0 ),0,null,'STATUS_LOOKUP_CODE ') ||

               

              nvl(length( CURRENCY_CODE),0) - nvl(length(trim( CURRENCY_CODE) ) ,0 ),0,null,'CURRENCY_CODE ') ||

               

              nvl(length( RATE_TYPE),0) - nvl(length(trim( RATE_TYPE) ) ,0 ),0,null,'RATE_TYPE ') ||

               

              nvl(length( FROM_TYPE_LOOKUP_CODE),0) - nvl(length(trim( FROM_TYPE_LOOKUP_CODE) ) ,0 ),0,null,'FROM_TYPE_LOOKUP_CODE ') ||

               

              nvl(length( AUTHORIZATION_STATUS),0) - nvl(length(trim( AUTHORIZATION_STATUS) ) ,0 ),0,null,'AUTHORIZATION_STATUS ') ||

               

              nvl(length( APPROVED_FLAG),0) - nvl(length(trim( APPROVED_FLAG) ) ,0 ),0,null,'APPROVED_FLAG ') ||

               

              nvl(length( NOTE_TO_AUTHORIZER),0) - nvl(length(trim( NOTE_TO_AUTHORIZER) ) ,0 ),0,null,'NOTE_TO_AUTHORIZER ') ||

               

              nvl(length( NOTE_TO_VENDOR),0) - nvl(length(trim( NOTE_TO_VENDOR) ) ,0 ),0,null,'NOTE_TO_VENDOR ') ||

               

              nvl(length( NOTE_TO_RECEIVER),0) - nvl(length(trim( NOTE_TO_RECEIVER) ) ,0 ),0,null,'NOTE_TO_RECEIVER ') ||

               

              nvl(length( VENDOR_ORDER_NUM),0) - nvl(length(trim( VENDOR_ORDER_NUM) ) ,0 ),0,null,'VENDOR_ORDER_NUM ') ||

               

              nvl(length( CONFIRMING_ORDER_FLAG),0) - nvl(length(trim( CONFIRMING_ORDER_FLAG) ) ,0 ),0,null,'CONFIRMING_ORDER_FLAG ') ||

               

              nvl(length( REPLY_METHOD_LOOKUP_CODE),0) - nvl(length(trim( REPLY_METHOD_LOOKUP_CODE) ) ,0 ),0,null,'REPLY_METHOD_LOOKUP_CODE ') ||

               

              nvl(length( QUOTE_TYPE_LOOKUP_CODE),0) - nvl(length(trim( QUOTE_TYPE_LOOKUP_CODE) ) ,0 ),0,null,'QUOTE_TYPE_LOOKUP_CODE ') ||

               

              nvl(length( QUOTATION_CLASS_CODE),0) - nvl(length(trim( QUOTATION_CLASS_CODE) ) ,0 ),0,null,'QUOTATION_CLASS_CODE ') ||

               

              nvl(length( QUOTE_WARNING_DELAY_UNIT),0) - nvl(length(trim( QUOTE_WARNING_DELAY_UNIT) ) ,0 ),0,null,'QUOTE_WARNING_DELAY_UNIT ') ||

               

              nvl(length( QUOTE_VENDOR_QUOTE_NUMBER),0) - nvl(length(trim( QUOTE_VENDOR_QUOTE_NUMBER) ) ,0 ),0,null,'QUOTE_VENDOR_QUOTE_NUMBER ') ||

               

              nvl(length( ACCEPTANCE_REQUIRED_FLAG),0) - nvl(length(trim( ACCEPTANCE_REQUIRED_FLAG) ) ,0 ),0,null,'ACCEPTANCE_REQUIRED_FLAG ') ||

               

              nvl(length( USER_HOLD_FLAG),0) - nvl(length(trim( USER_HOLD_FLAG) ) ,0 ),0,null,'USER_HOLD_FLAG ') ||

               

              nvl(length( APPROVAL_REQUIRED_FLAG),0) - nvl(length(trim( APPROVAL_REQUIRED_FLAG) ) ,0 ),0,null,'APPROVAL_REQUIRED_FLAG ') ||

               

              nvl(length( CANCEL_FLAG),0) - nvl(length(trim( CANCEL_FLAG) ) ,0 ),0,null,'CANCEL_FLAG ') ||

               

              nvl(length( FIRM_STATUS_LOOKUP_CODE),0) - nvl(length(trim( FIRM_STATUS_LOOKUP_CODE) ) ,0 ),0,null,'FIRM_STATUS_LOOKUP_CODE ') ||

               

              nvl(length( FROZEN_FLAG),0) - nvl(length(trim( FROZEN_FLAG) ) ,0 ),0,null,'FROZEN_FLAG ') ||

               

              nvl(length( SUPPLY_AGREEMENT_FLAG),0) - nvl(length(trim( SUPPLY_AGREEMENT_FLAG) ) ,0 ),0,null,'SUPPLY_AGREEMENT_FLAG ') ||

               

              nvl(length( EDI_PROCESSED_FLAG),0) - nvl(length(trim( EDI_PROCESSED_FLAG) ) ,0 ),0,null,'EDI_PROCESSED_FLAG ') ||

               

              nvl(length( EDI_PROCESSED_STATUS),0) - nvl(length(trim( EDI_PROCESSED_STATUS) ) ,0 ),0,null,'EDI_PROCESSED_STATUS ') ||

               

              nvl(length( ATTRIBUTE_CATEGORY),0) - nvl(length(trim( ATTRIBUTE_CATEGORY) ) ,0 ),0,null,'ATTRIBUTE_CATEGORY ') ||

               

              nvl(length( ATTRIBUTE1),0) - nvl(length(trim( ATTRIBUTE1) ) ,0 ),0,null,'ATTRIBUTE1 ') ||

               

              nvl(length( ATTRIBUTE2),0) - nvl(length(trim( ATTRIBUTE2) ) ,0 ),0,null,'ATTRIBUTE2 ') ||

               

              nvl(length( ATTRIBUTE3),0) - nvl(length(trim( ATTRIBUTE3) ) ,0 ),0,null,'ATTRIBUTE3 ') ||

               

              nvl(length( ATTRIBUTE4),0) - nvl(length(trim( ATTRIBUTE4) ) ,0 ),0,null,'ATTRIBUTE4 ') ||

               

              nvl(length( ATTRIBUTE5),0) - nvl(length(trim( ATTRIBUTE5) ) ,0 ),0,null,'ATTRIBUTE5 ') ||

               

              nvl(length( ATTRIBUTE6),0) - nvl(length(trim( ATTRIBUTE6) ) ,0 ),0,null,'ATTRIBUTE6 ') ||

               

              nvl(length( ATTRIBUTE7),0) - nvl(length(trim( ATTRIBUTE7) ) ,0 ),0,null,'ATTRIBUTE7 ') ||

               

              nvl(length( ATTRIBUTE8),0) - nvl(length(trim( ATTRIBUTE8) ) ,0 ),0,null,'ATTRIBUTE8 ') ||

               

              nvl(length( ATTRIBUTE9),0) - nvl(length(trim( ATTRIBUTE9) ) ,0 ),0,null,'ATTRIBUTE9 ') ||

               

              nvl(length( ATTRIBUTE10),0) - nvl(length(trim( ATTRIBUTE10) ) ,0 ),0,null,'ATTRIBUTE10 ') ||

               

              nvl(length( ATTRIBUTE11),0) - nvl(length(trim( ATTRIBUTE11) ) ,0 ),0,null,'ATTRIBUTE11 ') ||

               

              nvl(length( ATTRIBUTE12),0) - nvl(length(trim( ATTRIBUTE12) ) ,0 ),0,null,'ATTRIBUTE12 ') ||

               

              nvl(length( ATTRIBUTE13),0) - nvl(length(trim( ATTRIBUTE13) ) ,0 ),0,null,'ATTRIBUTE13 ') ||

               

              nvl(length( ATTRIBUTE14),0) - nvl(length(trim( ATTRIBUTE14) ) ,0 ),0,null,'ATTRIBUTE14 ') ||

               

              nvl(length( ATTRIBUTE15),0) - nvl(length(trim( ATTRIBUTE15) ) ,0 ),0,null,'ATTRIBUTE15 ') ||

               

              nvl(length( CLOSED_CODE),0) - nvl(length(trim( CLOSED_CODE) ) ,0 ),0,null,'CLOSED_CODE ') ||

               

              nvl(length( USSGL_TRANSACTION_CODE),0) - nvl(length(trim( USSGL_TRANSACTION_CODE) ) ,0 ),0,null,'USSGL_TRANSACTION_CODE ') ||

               

              nvl(length( GOVERNMENT_CONTEXT),0) - nvl(length(trim( GOVERNMENT_CONTEXT) ) ,0 ),0,null,'GOVERNMENT_CONTEXT ') ||

               

              nvl(length( SHIPPING_CONTROL),0) - nvl(length(trim( SHIPPING_CONTROL) ) ,0 ),0,null,'SHIPPING_CONTROL ') fix_cols


              from apps.po_headers_all

              where

              decode( nvl(length( ENABLE_ALL_SITES),0) - nvl(length(trim( ENABLE_ALL_SITES) ) ,0 ),0,null,'ENABLE_ALL_SITES ') ||

               

              nvl(length( CREATED_LANGUAGE),0) - nvl(length(trim( CREATED_LANGUAGE) ) ,0 ),0,null,'CREATED_LANGUAGE ') ||

               

              nvl(length( LAST_UPDATED_PROGRAM),0) - nvl(length(trim( LAST_UPDATED_PROGRAM) ) ,0 ),0,null,'LAST_UPDATED_PROGRAM ') ||

               

              nvl(length( OTM_STATUS_CODE),0) - nvl(length(trim( OTM_STATUS_CODE) ) ,0 ),0,null,'OTM_STATUS_CODE ') ||

               

              nvl(length( OTM_RECOVERY_FLAG),0) - nvl(length(trim( OTM_RECOVERY_FLAG) ) ,0 ),0,null,'OTM_RECOVERY_FLAG ') ||

               

              nvl(length( TYPE_LOOKUP_CODE),0) - nvl(length(trim( TYPE_LOOKUP_CODE) ) ,0 ),0,null,'TYPE_LOOKUP_CODE ') ||

               

              nvl(length( GLOBAL_AGREEMENT_FLAG),0) - nvl(length(trim( GLOBAL_AGREEMENT_FLAG) ) ,0 ),0,null,'GLOBAL_AGREEMENT_FLAG ') ||

               

              nvl(length( CONSIGNED_CONSUMPTION_FLAG),0) - nvl(length(trim( CONSIGNED_CONSUMPTION_FLAG) ) ,0 ),0,null,'CONSIGNED_CONSUMPTION_FLAG ') ||

               

              nvl(length( CONSUME_REQ_DEMAND_FLAG),0) - nvl(length(trim( CONSUME_REQ_DEMAND_FLAG) ) ,0 ),0,null,'CONSUME_REQ_DEMAND_FLAG ') ||

               

              nvl(length( CHANGE_REQUESTED_BY),0) - nvl(length(trim( CHANGE_REQUESTED_BY) ) ,0 ),0,null,'CHANGE_REQUESTED_BY ') ||

               

              nvl(length( XML_FLAG),0) - nvl(length(trim( XML_FLAG) ) ,0 ),0,null,'XML_FLAG ') ||

               

              nvl(length( CONTERMS_EXIST_FLAG),0) - nvl(length(trim( CONTERMS_EXIST_FLAG) ) ,0 ),0,null,'CONTERMS_EXIST_FLAG ') ||

               

              nvl(length( ENCUMBRANCE_REQUIRED_FLAG),0) - nvl(length(trim( ENCUMBRANCE_REQUIRED_FLAG) ) ,0 ),0,null,'ENCUMBRANCE_REQUIRED_FLAG ') ||

               

              nvl(length( PENDING_SIGNATURE_FLAG),0) - nvl(length(trim( PENDING_SIGNATURE_FLAG) ) ,0 ),0,null,'PENDING_SIGNATURE_FLAG ') ||

               

              nvl(length( CHANGE_SUMMARY),0) - nvl(length(trim( CHANGE_SUMMARY) ) ,0 ),0,null,'CHANGE_SUMMARY ') ||

               

              nvl(length( DOCUMENT_CREATION_METHOD),0) - nvl(length(trim( DOCUMENT_CREATION_METHOD) ) ,0 ),0,null,'DOCUMENT_CREATION_METHOD ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE_CATEGORY),0) - nvl(length(trim( GLOBAL_ATTRIBUTE_CATEGORY) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE_CATEGORY ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE1),0) - nvl(length(trim( GLOBAL_ATTRIBUTE1) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE1 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE2),0) - nvl(length(trim( GLOBAL_ATTRIBUTE2) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE2 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE3),0) - nvl(length(trim( GLOBAL_ATTRIBUTE3) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE3 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE4),0) - nvl(length(trim( GLOBAL_ATTRIBUTE4) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE4 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE5),0) - nvl(length(trim( GLOBAL_ATTRIBUTE5) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE5 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE6),0) - nvl(length(trim( GLOBAL_ATTRIBUTE6) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE6 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE7),0) - nvl(length(trim( GLOBAL_ATTRIBUTE7) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE7 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE8),0) - nvl(length(trim( GLOBAL_ATTRIBUTE8) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE8 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE9),0) - nvl(length(trim( GLOBAL_ATTRIBUTE9) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE9 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE10),0) - nvl(length(trim( GLOBAL_ATTRIBUTE10) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE10 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE11),0) - nvl(length(trim( GLOBAL_ATTRIBUTE11) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE11 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE12),0) - nvl(length(trim( GLOBAL_ATTRIBUTE12) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE12 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE13),0) - nvl(length(trim( GLOBAL_ATTRIBUTE13) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE13 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE14),0) - nvl(length(trim( GLOBAL_ATTRIBUTE14) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE14 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE15),0) - nvl(length(trim( GLOBAL_ATTRIBUTE15) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE15 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE16),0) - nvl(length(trim( GLOBAL_ATTRIBUTE16) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE16 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE17),0) - nvl(length(trim( GLOBAL_ATTRIBUTE17) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE17 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE18),0) - nvl(length(trim( GLOBAL_ATTRIBUTE18) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE18 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE19),0) - nvl(length(trim( GLOBAL_ATTRIBUTE19) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE19 ') ||

               

              nvl(length( GLOBAL_ATTRIBUTE20),0) - nvl(length(trim( GLOBAL_ATTRIBUTE20) ) ,0 ),0,null,'GLOBAL_ATTRIBUTE20 ') ||

               

              nvl(length( INTERFACE_SOURCE_CODE),0) - nvl(length(trim( INTERFACE_SOURCE_CODE) ) ,0 ),0,null,'INTERFACE_SOURCE_CODE ') ||

               

              nvl(length( REFERENCE_NUM),0) - nvl(length(trim( REFERENCE_NUM) ) ,0 ),0,null,'REFERENCE_NUM ') ||

               

              nvl(length( WF_ITEM_TYPE),0) - nvl(length(trim( WF_ITEM_TYPE) ) ,0 ),0,null,'WF_ITEM_TYPE ') ||

               

              nvl(length( WF_ITEM_KEY),0) - nvl(length(trim( WF_ITEM_KEY) ) ,0 ),0,null,'WF_ITEM_KEY ') ||

               

              nvl(length( MRC_RATE_TYPE),0) - nvl(length(trim( MRC_RATE_TYPE) ) ,0 ),0,null,'MRC_RATE_TYPE ') ||

               

              nvl(length( MRC_RATE_DATE),0) - nvl(length(trim( MRC_RATE_DATE) ) ,0 ),0,null,'MRC_RATE_DATE ') ||

               

              nvl(length( MRC_RATE),0) - nvl(length(trim( MRC_RATE) ) ,0 ),0,null,'MRC_RATE ') ||

               

              nvl(length( PAY_ON_CODE),0) - nvl(length(trim( PAY_ON_CODE) ) ,0 ),0,null,'PAY_ON_CODE ') ||

               

              nvl(length( SEGMENT1),0) - nvl(length(trim( SEGMENT1) ) ,0 ),0,null,'SEGMENT1 ') ||

               

              nvl(length( SUMMARY_FLAG),0) - nvl(length(trim( SUMMARY_FLAG) ) ,0 ),0,null,'SUMMARY_FLAG ') ||

               

              nvl(length( ENABLED_FLAG),0) - nvl(length(trim( ENABLED_FLAG) ) ,0 ),0,null,'ENABLED_FLAG ') ||

               

              nvl(length( SEGMENT2),0) - nvl(length(trim( SEGMENT2) ) ,0 ),0,null,'SEGMENT2 ') ||

               

              nvl(length( SEGMENT3),0) - nvl(length(trim( SEGMENT3) ) ,0 ),0,null,'SEGMENT3 ') ||

               

              nvl(length( SEGMENT4),0) - nvl(length(trim( SEGMENT4) ) ,0 ),0,null,'SEGMENT4 ') ||

               

              nvl(length( SEGMENT5),0) - nvl(length(trim( SEGMENT5) ) ,0 ),0,null,'SEGMENT5 ') ||

               

              nvl(length( SHIP_VIA_LOOKUP_CODE),0) - nvl(length(trim( SHIP_VIA_LOOKUP_CODE) ) ,0 ),0,null,'SHIP_VIA_LOOKUP_CODE ') ||

               

              nvl(length( FOB_LOOKUP_CODE),0) - nvl(length(trim( FOB_LOOKUP_CODE) ) ,0 ),0,null,'FOB_LOOKUP_CODE ') ||

               

              nvl(length( FREIGHT_TERMS_LOOKUP_CODE),0) - nvl(length(trim( FREIGHT_TERMS_LOOKUP_CODE) ) ,0 ),0,null,'FREIGHT_TERMS_LOOKUP_CODE ') ||

               

              nvl(length( STATUS_LOOKUP_CODE),0) - nvl(length(trim( STATUS_LOOKUP_CODE) ) ,0 ),0,null,'STATUS_LOOKUP_CODE ') ||

               

              nvl(length( CURRENCY_CODE),0) - nvl(length(trim( CURRENCY_CODE) ) ,0 ),0,null,'CURRENCY_CODE ') ||

               

              nvl(length( RATE_TYPE),0) - nvl(length(trim( RATE_TYPE) ) ,0 ),0,null,'RATE_TYPE ') ||

               

              nvl(length( FROM_TYPE_LOOKUP_CODE),0) - nvl(length(trim( FROM_TYPE_LOOKUP_CODE) ) ,0 ),0,null,'FROM_TYPE_LOOKUP_CODE ') ||

               

              nvl(length( AUTHORIZATION_STATUS),0) - nvl(length(trim( AUTHORIZATION_STATUS) ) ,0 ),0,null,'AUTHORIZATION_STATUS ') ||

               

              nvl(length( APPROVED_FLAG),0) - nvl(length(trim( APPROVED_FLAG) ) ,0 ),0,null,'APPROVED_FLAG ') ||

               

              nvl(length( NOTE_TO_AUTHORIZER),0) - nvl(length(trim( NOTE_TO_AUTHORIZER) ) ,0 ),0,null,'NOTE_TO_AUTHORIZER ') ||

               

              nvl(length( NOTE_TO_VENDOR),0) - nvl(length(trim( NOTE_TO_VENDOR) ) ,0 ),0,null,'NOTE_TO_VENDOR ') ||

               

              nvl(length( NOTE_TO_RECEIVER),0) - nvl(length(trim( NOTE_TO_RECEIVER) ) ,0 ),0,null,'NOTE_TO_RECEIVER ') ||

               

              nvl(length( VENDOR_ORDER_NUM),0) - nvl(length(trim( VENDOR_ORDER_NUM) ) ,0 ),0,null,'VENDOR_ORDER_NUM ') ||

               

              nvl(length( CONFIRMING_ORDER_FLAG),0) - nvl(length(trim( CONFIRMING_ORDER_FLAG) ) ,0 ),0,null,'CONFIRMING_ORDER_FLAG ') ||

               

              nvl(length( REPLY_METHOD_LOOKUP_CODE),0) - nvl(length(trim( REPLY_METHOD_LOOKUP_CODE) ) ,0 ),0,null,'REPLY_METHOD_LOOKUP_CODE ') ||

               

              nvl(length( QUOTE_TYPE_LOOKUP_CODE),0) - nvl(length(trim( QUOTE_TYPE_LOOKUP_CODE) ) ,0 ),0,null,'QUOTE_TYPE_LOOKUP_CODE ') ||

               

              nvl(length( QUOTATION_CLASS_CODE),0) - nvl(length(trim( QUOTATION_CLASS_CODE) ) ,0 ),0,null,'QUOTATION_CLASS_CODE ') ||

               

              nvl(length( QUOTE_WARNING_DELAY_UNIT),0) - nvl(length(trim( QUOTE_WARNING_DELAY_UNIT) ) ,0 ),0,null,'QUOTE_WARNING_DELAY_UNIT ') ||

               

              nvl(length( QUOTE_VENDOR_QUOTE_NUMBER),0) - nvl(length(trim( QUOTE_VENDOR_QUOTE_NUMBER) ) ,0 ),0,null,'QUOTE_VENDOR_QUOTE_NUMBER ') ||

               

              nvl(length( ACCEPTANCE_REQUIRED_FLAG),0) - nvl(length(trim( ACCEPTANCE_REQUIRED_FLAG) ) ,0 ),0,null,'ACCEPTANCE_REQUIRED_FLAG ') ||

               

              nvl(length( USER_HOLD_FLAG),0) - nvl(length(trim( USER_HOLD_FLAG) ) ,0 ),0,null,'USER_HOLD_FLAG ') ||

               

              nvl(length( APPROVAL_REQUIRED_FLAG),0) - nvl(length(trim( APPROVAL_REQUIRED_FLAG) ) ,0 ),0,null,'APPROVAL_REQUIRED_FLAG ') ||

               

              nvl(length( CANCEL_FLAG),0) - nvl(length(trim( CANCEL_FLAG) ) ,0 ),0,null,'CANCEL_FLAG ') ||

               

              nvl(length( FIRM_STATUS_LOOKUP_CODE),0) - nvl(length(trim( FIRM_STATUS_LOOKUP_CODE) ) ,0 ),0,null,'FIRM_STATUS_LOOKUP_CODE ') ||

               

              nvl(length( FROZEN_FLAG),0) - nvl(length(trim( FROZEN_FLAG) ) ,0 ),0,null,'FROZEN_FLAG ') ||

               

              nvl(length( SUPPLY_AGREEMENT_FLAG),0) - nvl(length(trim( SUPPLY_AGREEMENT_FLAG) ) ,0 ),0,null,'SUPPLY_AGREEMENT_FLAG ') ||

               

              nvl(length( EDI_PROCESSED_FLAG),0) - nvl(length(trim( EDI_PROCESSED_FLAG) ) ,0 ),0,null,'EDI_PROCESSED_FLAG ') ||

               

              nvl(length( EDI_PROCESSED_STATUS),0) - nvl(length(trim( EDI_PROCESSED_STATUS) ) ,0 ),0,null,'EDI_PROCESSED_STATUS ') ||

               

              nvl(length( ATTRIBUTE_CATEGORY),0) - nvl(length(trim( ATTRIBUTE_CATEGORY) ) ,0 ),0,null,'ATTRIBUTE_CATEGORY ') ||

               

              nvl(length( ATTRIBUTE1),0) - nvl(length(trim( ATTRIBUTE1) ) ,0 ),0,null,'ATTRIBUTE1 ') ||

               

              nvl(length( ATTRIBUTE2),0) - nvl(length(trim( ATTRIBUTE2) ) ,0 ),0,null,'ATTRIBUTE2 ') ||

               

              nvl(length( ATTRIBUTE3),0) - nvl(length(trim( ATTRIBUTE3) ) ,0 ),0,null,'ATTRIBUTE3 ') ||

               

              nvl(length( ATTRIBUTE4),0) - nvl(length(trim( ATTRIBUTE4) ) ,0 ),0,null,'ATTRIBUTE4 ') ||

               

              nvl(length( ATTRIBUTE5),0) - nvl(length(trim( ATTRIBUTE5) ) ,0 ),0,null,'ATTRIBUTE5 ') ||

               

              nvl(length( ATTRIBUTE6),0) - nvl(length(trim( ATTRIBUTE6) ) ,0 ),0,null,'ATTRIBUTE6 ') ||

               

              nvl(length( ATTRIBUTE7),0) - nvl(length(trim( ATTRIBUTE7) ) ,0 ),0,null,'ATTRIBUTE7 ') ||

               

              nvl(length( ATTRIBUTE8),0) - nvl(length(trim( ATTRIBUTE8) ) ,0 ),0,null,'ATTRIBUTE8 ') ||

               

              nvl(length( ATTRIBUTE9),0) - nvl(length(trim( ATTRIBUTE9) ) ,0 ),0,null,'ATTRIBUTE9 ') ||

               

              nvl(length( ATTRIBUTE10),0) - nvl(length(trim( ATTRIBUTE10) ) ,0 ),0,null,'ATTRIBUTE10 ') ||

               

              nvl(length( ATTRIBUTE11),0) - nvl(length(trim( ATTRIBUTE11) ) ,0 ),0,null,'ATTRIBUTE11 ') ||

               

              nvl(length( ATTRIBUTE12),0) - nvl(length(trim( ATTRIBUTE12) ) ,0 ),0,null,'ATTRIBUTE12 ') ||

               

              nvl(length( ATTRIBUTE13),0) - nvl(length(trim( ATTRIBUTE13) ) ,0 ),0,null,'ATTRIBUTE13 ') ||

               

              nvl(length( ATTRIBUTE14),0) - nvl(length(trim( ATTRIBUTE14) ) ,0 ),0,null,'ATTRIBUTE14 ') ||

               

              nvl(length( ATTRIBUTE15),0) - nvl(length(trim( ATTRIBUTE15) ) ,0 ),0,null,'ATTRIBUTE15 ') ||

               

              nvl(length( CLOSED_CODE),0) - nvl(length(trim( CLOSED_CODE) ) ,0 ),0,null,'CLOSED_CODE ') ||

               

              nvl(length( USSGL_TRANSACTION_CODE),0) - nvl(length(trim( USSGL_TRANSACTION_CODE) ) ,0 ),0,null,'USSGL_TRANSACTION_CODE ') ||

               

              nvl(length( GOVERNMENT_CONTEXT),0) - nvl(length(trim( GOVERNMENT_CONTEXT) ) ,0 ),0,null,'GOVERNMENT_CONTEXT ') ||

               

              nvl(length( SHIPPING_CONTROL),0) - nvl(length(trim( SHIPPING_CONTROL) ) ,0 ),0,null,'SHIPPING_CONTROL ')

              is not null

               

              • 4. Re: FRM-40654: Record has been updated Requery block to see change
                J Reinhart

                One last note on the query -- if its just the one PO with the issue, you can put the PO number in the 'where' clause and just see what fields on that one have spaces.    Add     and segment1 = '<<your po number>>' to the where clause.   If the issue is in the lines, locations, or distributions, you can add    and po_header_id = <<po_header_id_from_po_header_query>>     to the where clause.

                 

                You also asked about how to correct the spaces.   Always try fixing it on the form first.  If the issue is in the Note_to_receiver, for example, and the user changes the note_to_receiver field to remove the spaces, then the form will expect that field to be different than what's in the table, and he may be able to save the updated record. 

                 

                If it won't allow the user to do that, it's possible to write sql to update the data, but for any update to a standard table, you should open an SR and get Oracle's approval to do it.   It's not supported -- or safe -- to take updating sql from open forums like this.  (I realize that sounds like a public service announcement, lol, but it is true)

                 

                Regards,

                 

                J