Skip to main content
Kofax

MarkView Advisor 6.4 reporting on invoices which have already been deleted from the ERP.

Summary

4762

Applies To

  • ERP System: Oracle
  • MarkView Version: 6.4
  • Advisor Version: 2.x and below

Behavior

  • MarkView Advisor is reporting on invoices that have already been deleted from the ERP system.

Known Causes

  • This is a known issue ( SPR00081744 )with MarkView Advisor 6.4 which results from the mvam_ap_inv_dlt_tr database trigger not adding 'InvoiceHeaderDelete' messages to the mvam_event_message table when an ERP record is deleted.

Resolution

  • This issue is resolved in MarkView Advisor 3.0 and higher.
  • For customers running MarkView Advisor 2.x this can be resolved by editing the following code in the mvam_ap_inv_dlt_tr database trigger:

FROM:
i := i + 1;
fieldNames(i) := 'business-unit';
newRec(i) := :new.org_id;
oldRec(i) := :old.org_id;

i := i + 1;
fieldNames(i) := 'invoice-id';
newRec(i) := :new.invoice_id;
oldRec(i) := :old.invoice_id;

TO:
i := i + 1;
fieldNames(i) := 'business-unit';
newRec(i) := :old.org_id;
oldRec(i) := :old.org_id;

i := i + 1;
fieldNames(i) := 'invoice-id';
newRec(i) := :old.invoice_id;
oldRec(i) := :old.invoice_id;

Follow Up:

While the steps above should prevent the issue from reoccurring, the attached AdvisorCleanupDeletedInvoices.sql, can be used to check your Advisor invoice data, and remove any records related to the invoices which have already been deleted from your ERP.

The script will need to be run in the MarkView schema, but you must first grant some additional privileges to the MarkView database user from the Advisor schema:

Log in to the Advisor schema and run:

  • SQL> grant select on mvac_invoice to markview;
  • SQL> grant select on mvac_business_unit to markview;

The log into the MarkView schema and run:

Note: Depending on the amount of data, the AdvisorCleanupDeletedInvoices.sql script may take some time to complete.

Keywords: report, delete