ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN A24AF8EFCDFF6E06E0530100007F1633Y select haouv.name operating_unit, pv.vendor_name supplier, pv.segment1 supplier_number, &dup_columns xxen_util.meaning(pv.vendor_type_lookup_code,'VENDOR TYPE',201) type, pv.type_1099 income_tax_type, pv.num_1099 taxpayer_id, pv.vat_registration_num tax_registration_number, pv0.vendor_name parent_supplier_name, pv0.segment1 parent_supplier_number, pv.customer_num, pv.vat_code, decode(pv.small_business_flag,'Y','Y') small_business_flag, decode(pv.hold_flag,'Y','Y') hold_flag, pv.purchasing_hold_reason, pv.min_order_amount, pv.price_tolerance, pvsa.vendor_site_code site_code, pvsa.address_line1, pvsa.address_line2, pvsa.address_line3, pvsa.address_line4, pvsa.city, pvsa.state, pvsa.zip, pvsa.county, pvsa.province, nvl(ftv.territory_short_name,pvsa.country) country, pvsa.area_code, pvsa.phone, pvsa.fax_area_code, pvsa.fax, pvsa.supplier_notif_method, pvsa.email_address, nvl(pvsa.terms_date_basis,pv.terms_date_basis) terms_date_basis, nvl(pvsa.pay_group_lookup_code,pv.pay_group_lookup_code) pay_group, nvl(att1.name,att0.name) payment_terms, xxen_util.meaning(nvl(pvsa.pay_date_basis_lookup_code,pv.pay_date_basis_lookup_code),'PAY DATE BASIS',201) pay_date_basis, xxen_util.client_time(pv.creation_date) supplier_creation_date, xxen_util.user_name(pv.created_by) supplier_created_by, xxen_util.client_time(pv.last_update_date) supplier_last_update_date, xxen_util.user_name(pv.last_updated_by) supplier_last_updated_by, xxen_util.client_time(pvsa.creation_date) site_creation_date, xxen_util.user_name(pvsa.created_by) site_created_by, xxen_util.client_time(pvsa.last_update_date) site_last_update_date, xxen_util.user_name(pvsa.last_updated_by) site_last_updated_by &contacts_columns from hr_all_organization_units_vl haouv, po_vendors pv, po_vendor_sites_all pvsa, fnd_territories_vl ftv, (select pvc.* from po_vendor_contacts pvc where '&show_contacts'='Y' and nvl(pvc.inactive_date,sysdate)>=sysdate) pvc, po_vendors pv0, ap_terms_tl att0, ap_terms_tl att1 &dup_from where 1=1 and pv.vendor_id=pvsa.vendor_id(+) and pvsa.org_id=haouv.organization_id(+) and pvsa.country=ftv.territory_code(+) and pvsa.vendor_site_id=pvc.vendor_site_id(+) and pv.parent_vendor_id=pv0.vendor_id(+) and pv.terms_id=att0.term_id(+) and pvsa.terms_id=att1.term_id(+) and att0.language(+)=userenv('lang') and att1.language(+)=userenv('lang') 49A8B1A803C008ACE0530100007FEB91 US AP Suppliers 11i AP suppliers (po vendors) including supplier sites and contact information Duplicate Match restricts the output to suppliers sharing a match key with at least one other supplier and lists the shared keys. Names are compared in upper case without punctuation, spaces and legal form suffixes (Inc, Ltd, GmbH, SA and similar), the other keys without punctuation and spaces. Bank accounts are shown masked. Spend Last 12 Months is the total of the supplier's invoices dated in the last twelve months, excluding cancelled invoices, in each operating unit's functional currency. Enginatics &contacts_columns &dup_columns &dup_from &show_contacts 1=1 5=5 1 10 1=1 haouv.name=:operating_unit LOV HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 Y select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') order by hou.name Y F Unité d'exploitation US Operating Unit 2 20 1=1 pv.vendor_name=:supplier_name LOV AP Supplier B9847D20A0E4742FE0538931640A6379 Y Y select aps.vendor_name value, aps.segment1 description from ap_suppliers aps where (:$flex$.operating_unit is null or aps.vendor_id in (select assa.vendor_id from hr_all_organization_units_vl haouv, ap_supplier_sites_all assa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=assa.org_id)) and (:$flex$.organization_code is null or aps.vendor_id in (select assa.vendor_id from org_organization_definitions ood, ap_supplier_sites_all assa where xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y' and ood.operating_unit=assa.org_id)) order by aps.vendor_name, aps.vendor_id F Fournisseur US Supplier 3 30 1=1 pv.segment1=:supplier_number LOV AP Supplier Number 8E2FF36EDF1479D2E0530100007F1FF2 Y select aps.segment1 value, aps.vendor_name description from ap_suppliers aps where :$flex$.operating_unit is null or aps.vendor_id in (select assa.vendor_id from hr_all_organization_units_vl haouv, ap_supplier_sites_all assa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=assa.org_id) order by aps.segment1, aps.vendor_id F Numéro de fournisseur US Supplier Number 4 40 1=1 pv.vendor_type_lookup_code=xxen_util.lookup_code(:vendor_type,'VENDOR TYPE',201) LOV AP Supplier Type 98567A11CE2765ADE0530100007F6157 Y select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='VENDOR TYPE' and flvv.view_application_id=201 and flvv.security_group_id=0 order by flvv.lookup_code F Type de fournisseur US Supplier Type 5 50 1=1 ftv.territory_short_name=:country LOV Country 8E2FF36EDE8379D2E0530100007F1FF2 Y select ftv.territory_short_name value, ftv.territory_code||': '||ftv.description description from fnd_territories_vl ftv order by ftv.territory_short_name F Pays US Country 6 60 &show_contacts Y LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual F Afficher les contacts US Show Contacts 7 &contacts_columns , pvc.prefix, pvc.first_name contact_first_name, pvc.middle_name contact_middle_name, pvc.last_name contact_last_name, pvc.title contact_title, pvc.area_code contact_area_code, pvc.phone contact_phone F Afficher les contacts US Show Contacts 8 70 5=5 w.match_type=:duplicate_match LOV custom Y select 'Name' value, 'Normalized supplier name' description from dual union all select 'Taxpayer ID' value, null description from dual union all select 'Tax Registration' value, null description from dual union all select 'Bank Account' value, null description from dual union all select 'Address' value, 'Site address line 1, postal code and country' description from dual US Duplicate Match Show only suppliers sharing the selected keys with another supplier. Names are compared without punctuation, spaces and legal form suffixes. 9 &dup_columns dup.duplicate_match, dup.spend_last_12_months, US Duplicate Match 10 &dup_from ,( select z.vendor_id, listagg(z.match_type||': '||z.match_display,'; ') within group (order by z.match_type, z.match_display) duplicate_match, (select sum(nvl(aia.base_amount,aia.invoice_amount)) from ap_invoices_all aia where z.vendor_id=aia.vendor_id and aia.invoice_date>=add_months(trunc(sysdate),-12) and aia.cancelled_date is null) spend_last_12_months from ( select y.vendor_id, y.match_type, y.match_display, row_number() over (partition by y.vendor_id order by y.match_type, y.match_display) match_number from ( select distinct x.vendor_id, x.match_type, substrb(x.match_display,1,100) match_display from ( select w.*, count(distinct w.vendor_id) over (partition by w.match_type, w.match_key) vendor_count from ( select pv.vendor_id, 'Name' match_type, replace(regexp_replace(' '||replace(regexp_replace(upper(pv.vendor_name),'[^[:alnum:]]+',' '),' ',' ')||' ',' (INC|INCORPORATED|LTD|LIMITED|LLC|CORP|CORPORATION|CO|COMPANY|GMBH|AG|SA|SARL|SAS|BV|NV|PLC|SPA|SRL|AB|OY|AS|PTY|KG) ',' '),' ') match_key, pv.vendor_name match_display from po_vendors pv union all select pv.vendor_id, 'Taxpayer ID' match_type, regexp_replace(upper(pv.num_1099),'[^[:alnum:]]') match_key, pv.num_1099 match_display from po_vendors pv union all select r.vendor_id, 'Tax Registration' match_type, regexp_replace(upper(r.registration_number),'[^[:alnum:]]') match_key, r.registration_number match_display from ( select pv.vendor_id, pv.vat_registration_num registration_number from po_vendors pv union all select pvsa.vendor_id, pvsa.vat_registration_num from po_vendor_sites_all pvsa ) r union all select abaua.vendor_id, 'Bank Account' match_type, coalesce(regexp_replace(upper(abaa.iban_number),'[^[:alnum:]]'),abaa.bank_branch_id||':'||regexp_replace(upper(abaa.bank_account_num),'[^[:alnum:]]')) match_key, lpad(substr(nvl(abaa.iban_number,abaa.bank_account_num),-4),length(nvl(abaa.iban_number,abaa.bank_account_num)),'X') match_display from ap_bank_account_uses_all abaua, ap_bank_accounts_all abaa where abaua.vendor_id is not null and nvl(abaua.end_date,sysdate)>=sysdate and abaua.external_bank_account_id=abaa.bank_account_id union all select pvsa.vendor_id, 'Address' match_type, regexp_replace(upper(pvsa.address_line1||pvsa.zip||pvsa.country),'[^[:alnum:]]') match_key, pvsa.address_line1||', '||pvsa.zip||' '||pvsa.city||', '||pvsa.country match_display from po_vendor_sites_all pvsa where pvsa.address_line1 is not null ) w where w.match_key is not null and 5=5 ) x where x.vendor_count>1 ) y ) z where z.match_number<=30 group by z.vendor_id ) dup US Duplicate Match 11 1=1 pv.vendor_id=dup.vendor_id US Duplicate Match 12 80 1=1 (regexp_like(pv.vendor_name,'^[[:space:]]|[[:space:]]$|[[:space:]]{2}|[[:cntrl:]]') or regexp_like(pvsa.vendor_site_code,'^[[:space:]]|[[:space:]]$|[[:space:]]{2}|[[:cntrl:]]') or regexp_like(pvsa.address_line1,'^[[:space:]]|[[:space:]]$|[[:space:]]{2}|[[:cntrl:]]')) LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Name Data Quality Issues Only Show only suppliers whose name, site code or address line 1 has leading, trailing or double spaces or control characters. 97211A3177054556ADDC0657529FCD4F Duplicate Supplier Bank Accounts Y Suppliers that share a bank account with another supplier, with the bank account details. SYSADMIN 9 ADDRESS_LINE1 10 COUNTRY 1 DUPLICATE_MATCH 1 7 OPERATING_UNIT 8 SITE_CODE 11 SPEND_LAST_12_MONTHS 2 SUPPLIER 12 SUPPLIER_CREATION_DATE 3 SUPPLIER_NUMBER 2 5 TAXPAYER_ID 6 TAX_REGISTRATION_NUMBER 4 TYPE Duplicate Match Bank Account S Site 30A08C8DE84D416B9C9780A9A3DAF172 Duplicate Suppliers Y Suppliers that share a normalized name, tax id, tax registration, bank account or address, with last invoice date and spend, to find and merge duplicate suppliers. SYSADMIN 9 ADDRESS_LINE1 10 COUNTRY 1 DUPLICATE_MATCH 1 7 OPERATING_UNIT 8 SITE_CODE 11 SPEND_LAST_12_MONTHS 2 SUPPLIER 12 SUPPLIER_CREATION_DATE 3 SUPPLIER_NUMBER 2 5 TAXPAYER_ID 6 TAX_REGISTRATION_NUMBER 4 TYPE Duplicate Match <multiple_values>Name;Taxpayer ID;Tax Registration;Bank Account;Address S Site 6731925EDFDE421DB1B0DC01C258A7F4 Supplier Name Data Quality Y Supplier names and sites with leading or trailing spaces, double spaces or control characters. SYSADMIN 5 ADDRESS_LINE1 3 OPERATING_UNIT 4 SITE_CODE 2 1 SUPPLIER 1 2 SUPPLIER_NUMBER Name Data Quality Issues Only Y S Site