Skip to main content
Kofax

MarkView ERP Integration - Invoice Creation Fraud Detection report problem.

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