ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 49A8B1A803C008ACE0530100007FEB91Y select haouv.name operating_unit, aps.vendor_name supplier, aps.segment1 supplier_number, &dup_columns aps.vendor_name_alt alternate_supplier, hp.known_as trading_partner, &dff_columns xxen_util.meaning(aps.vendor_type_lookup_code,'VENDOR TYPE',201) type, aps.type_1099 income_tax_type, decode(aps.organization_type_lookup_code,'INDIVIDUAL',aps.individual_1099,aps.num_1099) taxpayer_id, (select papf.employee_number from per_all_people_f papf where papf.person_id=aps.employee_id and rownum<=1) employee_number, (select territory_short_name from fnd_territories_vl ftv where ftv.territory_code = zptp0.country_code) dflt_rep_country_name, zptp0.rep_registration_number dflt_rep_registrn_number, zptp0.registration_type_code dflt_rep_registrn_type, xxen_util.meaning(zptp0.allow_offset_tax_flag,'YES_NO',0) allow_offset_tax, aps.vat_registration_num tax_registration_number, aps.tax_reporting_name, xxen_util.yes(aps.federal_reportable_flag) federal_reportable, xxen_util.yes(aps.state_reportable_flag) state_reportable, xxen_util.yes(aps.allow_awt_flag) allow_withholding_tax, (select aag.name from ap_awt_groups aag where aps.awt_group_id=aag.group_id) inv_withholding_tax_group, (select aag.name from ap_awt_groups aag where aps.pay_awt_group_id=aag.group_id) pay_withholding_tax_group, aps0.vendor_name parent_supplier_name, aps0.segment1 parent_supplier_number, aps.customer_num, aps.vat_code, xxen_util.yes(aps.small_business_flag) small_business, xxen_util.yes(aps.hold_flag) hold_flag, aps.purchasing_hold_reason, xxen_util.meaning(case when nvl(aps.end_date_active,trunc(sysdate)+1)>trunc(sysdate) then 'A' else 'I' end,'REGISTRY_STATUS',222) supplier_status, aps.end_date_active supplier_inactive_date, aps.min_order_amount, aps.price_tolerance, assa.vendor_site_code site_code, (select trunc(max(aia.invoice_date)) from ap_invoices_all aia where aia.vendor_site_id=assa.vendor_site_id) last_invoice_date, hps.party_site_name address_name, assa.vendor_site_code_alt alternate_site_name, xxen_util.yes(assa.primary_pay_site_flag) primary_pay_site, xxen_util.yes(assa.purchasing_site_flag) purchasing_site, xxen_util.yes(assa.rfq_only_site_flag) rfq_only, xxen_util.yes(assa.pay_site_flag) pay_site, xxen_util.yes(assa.pcard_site_flag) procurement_card, xxen_util.yes(assa.hold_unmatched_invoices_flag) matching_required, xxen_util.meaning(assa.pay_on_code,'PAY ON CODE',201) pay_on, xxen_util.meaning(assa.pay_on_receipt_summary_code,'ERS INVOICE_SUMMARY',201) invoice_summary_level, xxen_util.yes(assa.create_debit_memo_flag) create_debit_memo_from_rts, xxen_util.yes(assa.hold_future_payments_flag) hold_future_payments, xxen_util.yes(assa.hold_all_payments_flag) hold_all_payments, assa.inactive_date site_inactive_date, assa.freight_terms_lookup_code freight_terms, assa.fob_lookup_code free_on_board_code, assa.address_line1, assa.address_line2, assa.address_line3, assa.address_line4, assa.city, assa.state, assa.zip, assa.county, assa.province, nvl(ftv.territory_short_name,assa.country) country, assa.area_code, assa.phone, assa.fax_area_code, assa.fax, assa.supplier_notif_method, assa.email_address, nvl(assa.terms_date_basis,aps.terms_date_basis) terms_date_basis, nvl(assa.pay_group_lookup_code,aps.pay_group_lookup_code) pay_group, xxen_util.meaning(nvl(assa.pay_date_basis_lookup_code,aps.pay_date_basis_lookup_code),'PAY DATE BASIS',201) pay_date_basis, atv0.name payment_terms, (select territory_short_name from fnd_territories_vl ftv where ftv.territory_code = zptp1.country_code) site_dflt_rep_country_name, zptp1.rep_registration_number site_dflt_rep_registrn_number, zptp1.registration_type_code site_dflt_rep_registrn_type, xxen_util.meaning(assa.auto_tax_calc_override,'YES_NO',0) auto_tax_calculation_override, xxen_util.meaning(assa.auto_tax_calc_flag,'YES_NO',0) auto_tax_calculation, xxen_util.meaning(zptp1.allow_offset_tax_flag,'YES_NO',0) site_allow_offset_tax, (select zicv.meaning from zx_input_classifications_v zicv where zicv.lookup_code=zptp1.tax_classification_code and zicv.org_id in (assa.org_id,-99) and rownum<=1) site_tax_classification, xxen_util.meaning(assa.allow_awt_flag,'YES_NO',0) site_allow_withholding_tax, (select aag.name from ap_awt_groups aag where aag.group_id = assa.awt_group_id) site_inv_withholding_tax_group, (select aag.name from ap_awt_groups aag where aag.group_id = assa.pay_awt_group_id) site_pay_withholding_tax_group, (select distinct listagg(xxen_util.meaning(ieppm.payment_method_code,'PAYMENT METHOD',200,'Y'),', ') within group (order by ieppm.primary_flag desc, ieppm.payment_method_code) over () payment_method from iby_ext_party_pmt_mthds ieppm where iepa0.ext_payee_id=ieppm.ext_pmt_party_id) payment_method, (select gcck.concatenated_segments from gl_code_combinations_kfv gcck where assa.accts_pay_code_combination_id=gcck.code_combination_id) liability_account, (select gcck.concatenated_segments from gl_code_combinations_kfv gcck where assa.prepay_code_combination_id=gcck.code_combination_id) prepayment_account, (select gcck.concatenated_segments from gl_code_combinations_kfv gcck where assa.future_dated_payment_ccid=gcck.code_combination_id) bills_payable_account, &supplier_bank_account atv1.name site_payment_terms, (select distinct listagg(xxen_util.meaning(ieppm.payment_method_code,'PAYMENT METHOD',200,'Y'),', ') within group (order by ieppm.primary_flag desc, ieppm.payment_method_code) over () payment_method from iby_ext_party_pmt_mthds ieppm where iepa1.ext_payee_id=ieppm.ext_pmt_party_id) site_payment_method, &site_bank_account &contacts_columns &contact_responsibility_columns &il_reg_columns xxen_util.client_time(aps.creation_date) supplier_creation_date, xxen_util.user_name(aps.created_by) supplier_created_by, xxen_util.client_time(aps.last_update_date) supplier_last_update_date, xxen_util.user_name(aps.last_updated_by) supplier_last_updated_by, xxen_util.client_time(assa.creation_date) site_creation_date, xxen_util.user_name(assa.created_by) site_created_by, xxen_util.client_time(assa.last_update_date) site_last_update_date, xxen_util.user_name(assa.last_updated_by) site_last_updated_by, aps.vendor_id, assa.vendor_site_id from hr_all_organization_units_vl haouv, ap_suppliers aps, (select assa.* from ap_supplier_sites_all assa where 2=2) assa, fnd_territories_vl ftv, ( select pvc.vendor_contact_id, pvc.vendor_site_id, pvc.vendor_id, pvc.per_party_id, pvc.prefix, pvc.first_name, pvc.middle_name, pvc.last_name, pvc.title, pvc.area_code, pvc.phone, pvc.email_address, pvc.inactive_date from po_vendor_contacts pvc where '&show_contacts'='Y' and 3=3 union all select pvc.* from ( select to_number(null) vendor_contact_id, to_number(null) vendor_site_id, aps.vendor_id, hzr.subject_id per_party_id, nvl(hp.person_pre_name_adjunct,hp.salutation) prefix, substr(hp.person_first_name,1,15) first_name, substr(hp.person_middle_name,1,15) middle_name, substr(hp.person_last_name,1,15) last_name, substr(hp.person_title,1,30) title, hp2.primary_phone_area_code area_code, hp2.primary_phone_number phone, hp2.email_address, hzr.end_date inactive_date from ap_suppliers aps, hz_relationships hzr, hz_parties hp, hz_parties hp2 where aps.party_id=hzr.object_id and hzr.object_type='ORGANIZATION' and hzr.subject_type='PERSON' and hzr.relationship_type='CONTACT' and hzr.relationship_code='CONTACT_OF' and hzr.directional_flag='F' and hzr.status in ('A','I') and hzr.subject_id=hp.party_id and hzr.party_id=hp2.party_id and hzr.subject_id in (select hpua.party_id from hz_party_usg_assignments hpua where hpua.party_usage_code='SUPPLIER_CONTACT' and hpua.status_flag in ('A','I')) and not exists (select null from ap_supplier_contacts asco where hzr.relationship_id=asco.relationship_id and (asco.org_party_site_id is not null or asco.vendor_site_id is null)) ) pvc where '&show_contacts'='Y' and 3=3 ) pvc, ( select fu.person_party_id, listagg(fu.user_name,chr(10)) within group (order by fu.user_name) contact_user_name, case when count(*)=count(fu.end_date) then max(fu.end_date) end contact_user_inactive_date from fnd_user fu where '&show_contact_responsibilities'='Y' and fu.person_party_id in (select asco.per_party_id from ap_supplier_contacts asco union select hpua.party_id from hz_party_usg_assignments hpua where hpua.party_usage_code='SUPPLIER_CONTACT') group by fu.person_party_id ) cu, ( select x.person_party_id, listagg(x.responsibility_name,chr(10)) within group (order by x.responsibility_name, x.application_name) contact_responsibilities, listagg(x.application_name,chr(10)) within group (order by x.responsibility_name, x.application_name) contact_applications from ( select distinct fu.person_party_id, frv.responsibility_name, fav.application_name from fnd_user fu, fnd_user_resp_groups_direct furgd, fnd_responsibility_vl frv, fnd_application_vl fav where '&show_contact_responsibilities'='Y' and fu.person_party_id in (select asco.per_party_id from ap_supplier_contacts asco union select hpua.party_id from hz_party_usg_assignments hpua where hpua.party_usage_code='SUPPLIER_CONTACT') and fu.user_id=furgd.user_id and furgd.security_group_id=0 and (furgd.end_date is null or furgd.end_date>sysdate) and furgd.responsibility_id=frv.responsibility_id and furgd.responsibility_application_id=frv.application_id and furgd.responsibility_application_id=fav.application_id ) x group by x.person_party_id ) cr, ap_suppliers aps0, ap_terms_vl atv0, ap_terms_vl atv1, (select iepa.* from iby_external_payees_all iepa where '&show_bank_accounts'='Y' and iepa.payment_function='PAYABLES_DISB' and iepa.party_site_id is null and iepa.supplier_site_id is null and iepa.org_id is null) iepa0, (select iepa.* from iby_external_payees_all iepa where '&show_bank_accounts'='Y' and iepa.payment_function='PAYABLES_DISB' and iepa.party_site_id is not null) iepa1, (select ipiua.* from iby_pmt_instr_uses_all ipiua where 4=4 and ipiua.payment_function='PAYABLES_DISB') ipiua0, (select ipiua.* from iby_pmt_instr_uses_all ipiua where 4=4 and ipiua.payment_function='PAYABLES_DISB') ipiua1, iby_ext_bank_accounts ieba0, iby_ext_bank_accounts ieba1, ce_bank_branches_v cbbv0, ce_bank_branches_v cbbv1, hz_parties hp, hz_party_sites hps, zx_party_tax_profile zptp0, zx_party_tax_profile zptp1 &dup_from &il_from where 1=1 and aps.vendor_id=assa.vendor_id(+) and assa.org_id=haouv.organization_id(+) and assa.country=ftv.territory_code(+) and assa.vendor_id=pvc.vendor_id(+) and nvl(assa.vendor_site_id,-99)=nvl(pvc.vendor_site_id(+),nvl(assa.vendor_site_id,-99)) and pvc.per_party_id=cu.person_party_id(+) and pvc.per_party_id=cr.person_party_id(+) and aps.parent_vendor_id=aps0.vendor_id(+) and aps.terms_id=atv0.term_id(+) and assa.terms_id=atv1.term_id(+) and aps.party_id=iepa0.payee_party_id(+) and assa.party_site_id=iepa1.party_site_id(+) and assa.org_id=iepa1.org_id(+) and assa.vendor_site_id=iepa1.supplier_site_id(+) and iepa0.ext_payee_id=ipiua0.ext_pmt_party_id(+) and iepa1.ext_payee_id=ipiua1.ext_pmt_party_id(+) and decode(ipiua0.instrument_type,'BANKACCOUNT',ipiua0.instrument_id)=ieba0.ext_bank_account_id(+) and decode(ipiua1.instrument_type,'BANKACCOUNT',ipiua1.instrument_id)=ieba1.ext_bank_account_id(+) and ieba0.branch_id=cbbv0.branch_party_id(+) and ieba1.branch_id=cbbv1.branch_party_id(+) and aps.party_id=hp.party_id(+) and assa.party_site_id=hps.party_site_id(+) and aps.party_id = zptp0.party_id (+) and 'THIRD_PARTY' = zptp0.party_type_code (+) and assa.party_site_id = zptp1.party_id (+) and 'THIRD_PARTY_SITE' = zptp1.party_type_code (+) &il_where order by haouv.name, aps.vendor_name, assa.vendor_site_code Added parameters Duplicate Match, listing suppliers that share a normalized name, taxpayer ID, tax registration, bank account or address, with columns Duplicate Match and Spend Last 12 Months, and Name Data Quality Issues Only, plus templates Duplicate Suppliers, Duplicate Supplier Bank Accounts and Supplier Name Data Quality. AR AP Suppliers موردي نقاط الوصول مع أعمدة تسجيل GST بما في ذلك PAN وTAN ونوع بائع TDS وأرقام تسجيل الضرائب D AP Suppliers AP suppliers with GST registration columns including PAN, TAN, TDS vendor type, and tax registration numbers E AP Suppliers Proveedores de AP con columnas de registro GST incluyendo PAN, TAN, tipo de proveedor TDS y números de registro fiscal F AP Suppliers Fournisseurs AP avec colonnes d'enregistrement GST incluant PAN, TAN, type de fournisseur TDS et numéros d'enregistrement fiscal I AP Suppliers Fornitori AP con colonne di registrazione GST inclusi PAN, TAN, tipo fornitore TDS e numeri di registrazione fiscale JA AP Suppliers PAN、TAN、TDSベンダータイプ、税登録番号を含むGST登録列付きAPサプライヤー KO AP Suppliers PAN, TAN, TDS 공급업체 유형 및 세금 등록 번호를 포함한 GST 등록 열이 있는 AP 공급업체 PTB AP Suppliers Fornecedores AP com colunas de registro GST incluindo PAN, TAN, tipo de fornecedor TDS e números de registro fiscal RU AP Suppliers AP-поставщики с колонками регистрации GST, включая PAN, TAN, тип поставщика TDS и регистрационные номера S AP Suppliers AP-leverantörer med GST-registreringskolumner inklusive PAN, TAN, TDS-leverantörstyp och skatteregistreringsnummer TR AP Suppliers PAN, TAN, TDS satıcı türü ve vergi kayıt numaraları dahil GST kayıt sütunları ile AP tedarikçileri US AP Suppliers AP suppliers (po vendors) including supplier sites, contact and bank account information on vendor and site level When India Localization is installed, the following GST columns are also included: GST Registration No, GST PAN No, GST TAN No. 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. ZHS AP Suppliers 带有GST注册列的AP供应商，包括PAN、TAN、TDS供应商类型和税务注册号 Enginatics &contact_responsibility_columns &contacts_columns &dff_columns &dup_columns &dup_from &il_from &il_reg_columns &il_where &show_bank_accounts &show_contact_responsibilities &show_contacts &site_bank_account &supplier_bank_account 1=1 2=2 3=3 4=4 5=5 1 -20 1=1 :India_Localization=:India_Localization Char Y xxen_util.jai_installed US India Localization 2 &il_from ,(select jpr.party_id, jpr.party_site_id, coalesce(max(case when jprl.regime_id in (select jr.regime_id from jai_regimes jr where jr.regime_type='T') and jprl.registration_type_code in ('GST','GSTIN') then nvl(jprl.registration_number,jprl.secondary_registration_number) end),max(case when regexp_like(nvl(jprl.registration_number,jprl.secondary_registration_number),'^[0-9]{2}[A-Z]{5}[0-9]{4}[A-Z][0-9A-Z]Z[0-9A-Z]$') and jprl.regime_id in (select jr.regime_id from jai_regimes jr where jr.regime_type='T') then nvl(jprl.registration_number,jprl.secondary_registration_number) end)) gstin, max(decode(jprl.registration_type_code,'PAN',jprl.registration_number)) pan_site, max(decode(jprl.registration_type_code,'TAN',jprl.registration_number)) tan_site, max(decode(jprl.sec_registration_type_code,'PAN',jprl.secondary_registration_number)) sec_pan_site, max(decode(jprl.sec_registration_type_code,'TAN',jprl.secondary_registration_number)) sec_tan_site from jai_party_regs_v jpr, jai_party_reg_lines_v jprl where jpr.party_reg_id=jprl.party_reg_id and jpr.party_type_code='THIRD_PARTY_SITE' and jpr.supplier_flag='Y' and jprl.effective_from<=sysdate and (jprl.effective_to is null or jprl.effective_to>=sysdate) group by jpr.party_id, jpr.party_site_id) il_reg_site ,(select jpr.party_id, coalesce(max(case when jprl.regime_id in (select jr.regime_id from jai_regimes jr where jr.regime_type='T') and jprl.registration_type_code in ('GST','GSTIN') then nvl(jprl.registration_number,jprl.secondary_registration_number) end),max(case when regexp_like(nvl(jprl.registration_number,jprl.secondary_registration_number),'^[0-9]{2}[A-Z]{5}[0-9]{4}[A-Z][0-9A-Z]Z[0-9A-Z]$') and jprl.regime_id in (select jr.regime_id from jai_regimes jr where jr.regime_type='T') then nvl(jprl.registration_number,jprl.secondary_registration_number) end)) gstin_hdr, max(decode(jprl.registration_type_code,'PAN',jprl.registration_number)) pan_hdr, max(decode(jprl.registration_type_code,'TAN',jprl.registration_number)) tan_hdr, max(decode(jprl.sec_registration_type_code,'PAN',jprl.secondary_registration_number)) sec_pan_hdr, max(decode(jprl.sec_registration_type_code,'TAN',jprl.secondary_registration_number)) sec_tan_hdr from jai_party_regs_v jpr, jai_party_reg_lines_v jprl where jpr.party_reg_id=jprl.party_reg_id and jpr.party_type_code='THIRD_PARTY' and jpr.supplier_flag='Y' and jprl.effective_from<=sysdate and (jprl.effective_to is null or jprl.effective_to>=sysdate) group by jpr.party_id) il_reg_hdr Y US India Localization 3 &il_reg_columns nvl(il_reg_site.gstin,il_reg_hdr.gstin_hdr) "GST Registration No", nvl(il_reg_site.pan_site,nvl(il_reg_site.sec_pan_site,nvl(il_reg_hdr.pan_hdr,il_reg_hdr.sec_pan_hdr))) "GST PAN No", nvl(il_reg_site.tan_site,nvl(il_reg_site.sec_tan_site,nvl(il_reg_hdr.tan_hdr,il_reg_hdr.sec_tan_hdr))) "GST TAN No", Y US India Localization 4 &il_where and nvl(assa.vendor_site_id,0)=il_reg_site.party_site_id(+) and aps.vendor_id=il_reg_hdr.party_id(+) Y US India Localization 5 10 1=1 haouv.name=:operating_unit LOV HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 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 coalesce(xxen_util.default_operating_unit,xxen_util.previous_parameter_value(:parameter_id)) Y F Unité d'exploitation US Operating Unit 6 20 1=1 aps.vendor_name=:supplier_name LOV AP Supplier B9847D20A0E4742FE0538931640A6379 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 7 30 1=1 aps.segment1=:supplier_number LOV AP Supplier Number 8E2FF36EDF1479D2E0530100007F1FF2 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 8 40 1=1 lower(aps.vendor_name) like lower(:vendor_name)||'%' Char F Le fournisseur commence par US Supplier starts with 9 50 1=1 aps.creation_date>=:creation_date_from Date US Creation Date From 10 60 1=1 aps.creation_date<:creation_date_to+1 Date last_day(:$flex$.creation_date_from) US Creation Date To 11 70 1=1 assa.vendor_site_code=:vendor_site_code LOV AP Supplier Site 8E2FF36EDF0279D2E0530100007F1FF2 select distinct assa.vendor_site_code value, assa.address_line1||', '||assa.city||', '||nvl(ftv.territory_short_name,assa.country) description from hr_all_organization_units_vl haouv, ap_suppliers aps, ap_supplier_sites_all assa, fnd_territories_vl ftv where (:$flex$.ap_supplier is null or xxen_util.contains(:$flex$.ap_supplier,aps.vendor_name)='Y') and (:$flex$.ap_supplier_number is null or xxen_util.contains(:$flex$.ap_supplier_number,aps.segment1)='Y') and (:$flex$.operating_unit is null or xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y') and haouv.organization_id=assa.org_id and aps.vendor_id=assa.vendor_id and assa.country=ftv.territory_code(+) order by assa.vendor_site_code F Site du fournisseur US Supplier Site 12 80 1=1 aps.vendor_type_lookup_code=xxen_util.lookup_code(:vendor_type,'VENDOR TYPE',201) LOV AP Supplier Type 98567A11CE2765ADE0530100007F6157 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 13 90 1=1 ftv.territory_short_name=:country LOV Country 8E2FF36EDE8379D2E0530100007F1FF2 select ftv.territory_code id, ftv.territory_short_name value, ftv.territory_code||': '||ftv.description description from fnd_territories_vl ftv order by ftv.territory_short_name value F Pays US Country 14 100 &show_contacts Y LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual F Afficher les contacts US Show Contacts 15 &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, pvc.email_address contact_email, pvc.inactive_date contact_inactive_date, case when pvc.per_party_id is not null then xxen_util.meaning(nvl2(pvc.vendor_site_id,'SITE','SUPPLIER'),'POS_SBD_BYR_USAGE_LIST',0) end contact_assignment_level, F Afficher les contacts US Show Contacts 16 110 &show_bank_accounts Y LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual Y US Show Bank Accounts 17 &site_bank_account iepa1.remit_advice_email site_remit_advice_email, ipiua1.order_of_preference site_priority, case when ipiua1.instrument_payment_use_id is not null and iepa1.supplier_site_id is not null then xxen_util.meaning('SITE','POS_SBD_BYR_USAGE_LIST',0) when ipiua1.instrument_payment_use_id is not null and iepa1.org_id is not null then xxen_util.meaning('ADDRESS_OU','POS_SBD_BYR_USAGE_LIST',0) when ipiua1.instrument_payment_use_id is not null and iepa1.party_site_id is not null then xxen_util.meaning('ADDRESS','POS_SBD_BYR_USAGE_LIST',0) else null end site_bank_assignment_level, ipiua1.end_date site_bank_acct_inactive_date, cbbv1.bank_name site_bank_name, cbbv1.bank_number site_bank_number, cbbv1.country site_bank_country, cbbv1.eft_swift_code site_swift_code, cbbv1.bank_branch_name site_bank_branch, cbbv1.branch_number site_branch_number, cbbv1.bank_branch_type site_branch_type, ieba1.masked_iban site_iban, ieba1.masked_bank_account_num site_bank_account, ieba1.currency_code site_currency_code, US Show Bank Accounts 18 &supplier_bank_account iepa0.remit_advice_email, ipiua0.order_of_preference priority, xxen_util.meaning('SUPPLIER','POS_SBD_BYR_USAGE_LIST',0) bank_assignment_level, ipiua0.end_date bank_account_inactive_date, cbbv0.bank_name, cbbv0.bank_number, cbbv0.country bank_country, cbbv0.eft_swift_code swift_code, cbbv0.bank_branch_name bank_branch, cbbv0.branch_number, cbbv0.bank_branch_type branch_type, ieba0.masked_iban iban, ieba0.masked_bank_account_num bank_account, ieba0.currency_code, US Show Bank Accounts 19 120 1=1 sysdate between nvl(aps.start_date_active,sysdate) and nvl(aps.end_date_active,sysdate) LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual Y US Active Suppliers Only 20 130 2=2 nvl(assa.inactive_date,sysdate)>=sysdate LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual Y US Active Sites Only 21 140 3=3 nvl(pvc.inactive_date,sysdate)>=sysdate LOV custom select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual where :$flex$.show_contacts='Y' Y US Active Contacts Only 22 145 &show_contact_responsibilities Y LOV custom select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual where :$flex$.show_contacts='Y' US Show Contact Responsibilities 23 &contact_responsibility_columns cu.contact_user_name, cu.contact_user_inactive_date, cr.contact_responsibilities, cr.contact_applications, US Show Contact Responsibilities 24 150 4=4 sysdate between ipiua.start_date and nvl(ipiua.end_date,sysdate) LOV custom select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual where :$flex$.show_bank_accounts='Y' Y US Active Bank Accounts Only 25 160 &dff_columns select xxen_util.dff_columns(p_table_name=>'po_vendors',p_table_alias=>'aps',p_column_name_prefix=>'Supplier: ',p_display_mode=>:dff_display) from dual union all select xxen_util.dff_columns(p_table_name=>'ap_supplier_sites_all',p_column_name_prefix=>'Site: ',p_display_mode=>:dff_display) from dual LOV Blitz Report DFF Display 53B31EFFC6B85470E0630100007F501D select 'V' id, 'Value' value, 'Value only' description from dual union all select 'C' id, 'Value: Description' value, 'Concatenated value and description' description from dual union all select 'S' id, 'Value, Description' value, 'Value and description in separate columns' description from dual fnd_profile.value('XXEN_REPORT_DFF_DISPLAY') US DFF Display 26 170 5=5 w.match_type=:duplicate_match LOV custom 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. 27 &dup_columns dup.duplicate_match, dup.spend_last_12_months, US Duplicate Match 28 &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 aps.vendor_id, 'Name' match_type, replace(regexp_replace(' '||replace(regexp_replace(upper(aps.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, aps.vendor_name match_display from ap_suppliers aps union all select aps.vendor_id, 'Taxpayer ID' match_type, regexp_replace(upper(decode(aps.organization_type_lookup_code,'INDIVIDUAL',aps.individual_1099,aps.num_1099)),'[^[:alnum:]]') match_key, decode(aps.organization_type_lookup_code,'INDIVIDUAL',aps.individual_1099,aps.num_1099) match_display from ap_suppliers aps 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 aps.vendor_id, aps.vat_registration_num registration_number from ap_suppliers aps union all select assa.vendor_id, assa.vat_registration_num from ap_supplier_sites_all assa union all select aps.vendor_id, zptp.rep_registration_number from ap_suppliers aps, zx_party_tax_profile zptp where aps.party_id=zptp.party_id and zptp.party_type_code='THIRD_PARTY' union all select assa.vendor_id, zptp.rep_registration_number from ap_supplier_sites_all assa, zx_party_tax_profile zptp where assa.party_site_id=zptp.party_id and zptp.party_type_code='THIRD_PARTY_SITE' ) r union all select aps.vendor_id, 'Bank Account' match_type, coalesce(ieba.iban_hash1,regexp_replace(upper(ieba.iban),'[^[:alnum:]]'),ieba.country_code||':'||ieba.bank_id||':'||ieba.branch_id||':'||coalesce(ieba.bank_account_num_hash1,regexp_replace(upper(ieba.bank_account_num),'[^[:alnum:]]'))) match_key, nvl(ieba.masked_iban,ieba.masked_bank_account_num) match_display from ap_suppliers aps, iby_account_owners iao, iby_ext_bank_accounts ieba where aps.party_id=iao.account_owner_party_id and nvl(iao.end_date,sysdate)>=sysdate and iao.ext_bank_account_id=ieba.ext_bank_account_id union all select assa.vendor_id, 'Address' match_type, regexp_replace(upper(hl.address1||hl.postal_code||hl.country),'[^[:alnum:]]') match_key, hl.address1||', '||hl.postal_code||' '||hl.city||', '||hl.country match_display from ap_supplier_sites_all assa, hz_locations hl where assa.location_id=hl.location_id and hl.address1 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 29 1=1 aps.vendor_id=dup.vendor_id US Duplicate Match 30 180 1=1 (regexp_like(aps.vendor_name,'^[[:space:]]|[[:space:]]$|[[:space:]]{2}|[[:cntrl:]]') or regexp_like(assa.vendor_site_code,'^[[:space:]]|[[:space:]]$|[[:space:]]{2}|[[:cntrl:]]') or regexp_like(assa.address_line1,'^[[:space:]]|[[:space:]]$|[[:space:]]{2}|[[:cntrl:]]')) LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 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. 2230859A56134EF599CC62700F16ED7E Duplicate Supplier Bank Accounts Y Suppliers that share a bank account with another supplier, with the bank account details. SYSADMIN 9 ADDRESS_LINE1 17 BANK_ACCOUNT 16 BANK_BRANCH 15 BANK_NAME 10 COUNTRY 1 DUPLICATE_MATCH 1 18 IBAN 11 LAST_INVOICE_DATE 7 OPERATING_UNIT 21 SITE_BANK_ACCOUNT 20 SITE_BANK_BRANCH 19 SITE_BANK_NAME 8 SITE_CODE 22 SITE_IBAN 12 SPEND_LAST_12_MONTHS 2 SUPPLIER 13 SUPPLIER_CREATION_DATE 14 SUPPLIER_INACTIVE_DATE 3 SUPPLIER_NUMBER 2 5 TAXPAYER_ID 6 TAX_REGISTRATION_NUMBER 4 TYPE Active Suppliers Only Y Duplicate Match Bank Account Show Bank Accounts Y S Site D5192202C22449D2A67B8FEDBBA22CC4 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 11 LAST_INVOICE_DATE 7 OPERATING_UNIT 8 SITE_CODE 12 SPEND_LAST_12_MONTHS 2 SUPPLIER 13 SUPPLIER_CREATION_DATE 14 SUPPLIER_INACTIVE_DATE 3 SUPPLIER_NUMBER 2 5 TAXPAYER_ID 6 TAX_REGISTRATION_NUMBER 4 TYPE Active Suppliers Only Y Duplicate Match <multiple_values>Name;Taxpayer ID;Tax Registration;Bank Account;Address S Site 9B3EF09F576241D190FF7FEBBE426D39 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