MarkView ERP Integration - Invoice Creation Fraud Detection report problem.

Last updated
Save as PDF

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