MarkView ERP Integration - FRM-40654: Record Has Been Updated By Another User. Re-Query To See Change

Applies To

• ERP System: All
• MarkView Version: All

Behavior

• When processing invoices in the Oracle Quick Invoices form the user receives the following message:
      FRM-40654: Record Has Been Updated By Another User. Re-Query To See Change

Known Causes

• A number of bug fixes were released over time to modify the functionality of the the APXIISIM.fmb or to further refine issues related to locking records. These bug fixes are R12 related. Further information on these bug fixes is available from the Oracle Support site and includes the following bugs:
  • Bug 9105666 : APXIIMPT SHOULD INCLUDE VENDOR_SITE_ID WHEN REJECTING DUPLICATE INVOICE NUMBER
    • Not working when party_site_id in AP_INVOICES_INTERFACE is not equal to party_site_id in PO_VENDOR_SITES
  • Bug 13933430 : AP R1213: APXIISIM, DUPLICATE INVOICE NUMBERS WITH SAME SUPPLIER
    • Still has an issue when attempting to lock a record when party_site_id is not passed to LOCK_ROW; as Null is compared with non-empty value inserted in AP_INVOICES_INTERFACE
  • Bug 16361548 : CANNOT UPDATE INVOICES FROM QUICK INVOICE GATEWAY
    • Still has the same error on locking the record if party_site_id in AP_INVOICES_INTERFACE is not the same as in PO_VENDOR_SITE

Resolution

• Since the MarkView Connector does not use the party_site_id field we can remove this from the MarkView integrated Quick Invoices Form which will roll back Bug 16361548 for the MarkView Quick Invoices Form and will resolve the issue.
  • Take a backup of the SFXIISIM.fmb form before making any changes to ensure you can always revert any changes.
    • If your form only has some of the bug fixes applied then only the changes relevant to these bug fixes will be required. Follow to steps below to check if any changes are needed.
  • Update the MarkView Quick Invoices Form (SFXIISIM.fmb) by:
    • Commenting the following line in LOCK_ROW and UPDATE_ROW procedures (in INVOICES_FOLDER_EVENT_LOCK*(Package Body) and INVOICES_FOLDER_EVENT_UPDATE*(Package Body) Program Units).
          x_party_site_id => :invoices_folder.party_site_id /*Bug 16361548*/
    • Altering the PRE_UPDATE procedure in the INVOICES_FOLDER_EVENT*(Package Body) Program Unit as described below:
      
      From:

      select count(1)
      into x_cnt_inv_int
      from ap_invoices_interface
      where invoice_num = :invoices_folder.invoice_num
      and vendor_id = :invoices_folder.vendor_id
      and (party_site_id = :invoices_folder.party_site_id /*Bug9105666*/
      OR (party_site_id is null and :invoices_folder.party_site_id is null)) /*Bug9105666*/
      and ((:invoices_folder.row_id is null) or (rowid != :invoices_folder.row_id));

      
      
      To (if you want to allow the same invoice number for another vendor_site_id):

      select count(1)
      into x_cnt_inv_int
      from ap_invoices_interface
      where invoice_num = :invoices_folder.invoice_num
      and vendor_id = :invoices_folder.vendor_id
      and (vendor_site_id = :invoices_folder.vendor_site_id /*Bug9105666*/
      OR (vendor_site_id is null and :invoices_folder.vendor_site_id is null)) /*Bug9105666*/
      and ((:invoices_folder.row_id is null) or (rowid != :invoices_folder.row_id));

      
      
      Or To (if you don’t want to allow the same invoice number for the same vendor twice):

      select count(1)
      into x_cnt_inv_int
      from ap_invoices_interface
      where invoice_num = :invoices_folder.invoice_num
      and vendor_id = :invoices_folder.vendor_id
      and ((:invoices_folder.row_id is null) or (rowid != :invoices_folder.row_id));

Keywords: Quick Invoices, Quick, FRM-40654, Re-Query