MarkView ERP Integration - Invoice Creation Fraud Detection report problem.
4206
Summary
4206
Applies To
- ERP System: ( Oracle )
- MarkView Version: (All Versions.)
Behavior
- Invoice Creation Fraud Detection report does not return items where a user updated the vendor site and created an invoice for this vendor.
Note: The appropriate checkpoint DOES trigger and the item DOES transition to the Invoice Entry Review queue, but when the user runs the interactive query for Invoice
Known Causes
- This has been identified as a defect (24173) -and has been assigned to our product development department. The defect will be scoped and corrected in an upcoming version of MarkView.
Resolution
- In the meantime, the following query can be used to correctly list the items that should be reported in the Invoice Creation Fraud Detection report.
Note: The attached 24173_ManualWorkaroundSteps.txt file details the steps to take in Module Admin to implement this updated query
select distinct '~HIDE~' || aia.invoice_id, ' ~DOCUMENT~' || 'AP_INVOICES', pov.vendor_name, povsa.vendor_site_code, aia.invoice_num, '' || nvl(to_char(aia.invoice_date, 'DD-MON-YYYY'), ' '), decode(nvl(aia.created_by, -1), nvl(pov.created_by, -2), fuic.user_name, nvl(pov.last_updated_by, -2), fuic.user_name, nvl(povsa.created_by, -2), fuic.user_name, nvl(povsa.last_updated_by, -2), fuic.user_name, decode(nvl(aia.last_updated_by, -1), nvl(pov.created_by, -2), fuiu.user_name, nvl(pov.last_updated_by, -2), fuiu.user_name, nvl(povsa.created_by, -2), fuiu.user_name, nvl(povsa.last_updated_by, -2), fuiu.user_name, '')), decode(nvl(aia.created_by, -1), nvl(pov.created_by, -2), 'Created both Invoice and Supplier', nvl(pov.last_updated_by, -2), 'Created Invoice and updated Supplier', nvl(povsa.created_by, -2), 'Created both Invoice and Supplier Site', nvl(povsa.last_updated_by, -2), 'Created Invoice and updated Supplier Site', decode(nvl(aia.last_updated_by, -1), nvl(pov.created_by, -2), 'Updated Invoice and created Supplier', nvl(povsa.last_updated_by, -2), 'Updated Invoice and Supplier Site', nvl(povsa.created_by, -2), 'Updated Invoice and created Supplier Site', nvl(pov.last_updated_by, -2), 'Updated both Invoice and Supplier', ' ')), aia.invoice_date from ap_invoices_all aia, po_vendors pov, po_vendor_sites_all povsa, fnd_user fuic, fnd_user fuiu where aia.vendor_id = pov.vendor_id and aia.vendor_site_id = povsa.vendor_site_id and (nvl(aia.created_by, -1) = nvl(pov.created_by, -2) or nvl(aia.created_by, -1) = nvl(pov.last_updated_by, -2) or nvl(aia.last_updated_by, -1) = nvl(pov.created_by, -2) or nvl(aia.last_updated_by, -1) = nvl(pov.last_updated_by, -2) or nvl(aia.created_by, -1) = nvl(povsa.created_by, -2) or nvl(aia.created_by, -1) = nvl(povsa.last_updated_by, -2) or nvl(aia.last_updated_by, -1) = nvl(povsa.created_by, -2) or nvl(aia.last_updated_by, -1) = nvl(povsa.last_updated_by, -2)) and nvl(aia.created_by, -1) = fuic.user_id(+) and nvl(aia.last_updated_by, -1) = fuiu.user_id(+) and (aia.invoice_date between to_date('01-JAN-2006', 'DD-MON-YYYY') and to_date('31-DEC-2006', 'DD-MON-YYYY')) order by pov.vendor_name, povsa.vendor_site_code, aia.invoice_num;
Note: The following is the old, incorrect query:
select distinct '~HIDE~' || aia.invoice_id, '~DOCUMENT~' || 'AP_INVOICES', pov.vendor_name, aia.invoice_num, '' || nvl(to_char(aia.invoice_date, 'DD-MON-YYYY'), ' '), decode(nvl(aia.created_by, -1), nvl(pov.created_by, -2), fuic.user_name, nvl(pov.last_updated_by, -2), fuic.user_name, decode(nvl(aia.last_updated_by, -3), nvl(pov.created_by, -2), fuiu.user_name, nvl(pov.last_updated_by, -2), fuiu.user_name, '')), decode(nvl(aia.created_by, -1), nvl(pov.created_by, -2), 'Created both Invoice and Supplier', nvl(pov.last_updated_by, -2), 'Created Invoice and updated Supplier', decode(nvl(aia.last_updated_by, -3), nvl(pov.created_by, -2), 'Updated Invoice and created Supplier', nvl(pov.last_updated_by, -2), 'Updated both Invoice and Supplier', '')), aia.invoice_date from ap_invoices_all aia, po_vendors pov, fnd_user fuic, fnd_user fuiu where aia.vendor_id = pov.vendor_id and (nvl(aia.created_by, -1) = nvl(pov.created_by, -2) or nvl(aia.created_by, -1) = nvl(pov.last_updated_by, -2) or nvl(aia.last_updated_by, -1) = nvl(pov.created_by, -2) or nvl(aia.last_updated_by, -1) = nvl(pov.last_updated_by, -2)) and nvl(aia.created_by, -1) = fuic.user_id(+) and nvl(aia.last_updated_by, -1) = fuiu.user_id(+) and (aia.invoice_date between to_date('01-JAN-2006', 'DD-MON-YYYY') and to_date('01-DEC-2006', 'DD-MON-YYYY')) order by pov.vendor_name, aia.invoice_num;
Keywords: 24173