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