ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 8D90D4BE91E7609CE0530100007FA2E5Y with x as ( select gp.min_start_date start_date from ( select min(gp.start_date) over (partition by gp.period_year) min_start_date, gp.start_date, gp.end_date from gl_periods gp where (gp.period_set_name, gp.period_type) in (select gl.period_set_name, gl.accounted_period_type from gl_ledgers gl where gl.object_type_code='L' and gl.ledger_category_code='PRIMARY' and rownum=1) and gp.adjustment_period_flag='N' ) gp where :as_of_date between gp.start_date and gp.end_date ) select distinct &lp_columns aps.vendor_name supplier_name, xxen_util.meaning(aps.vendor_type_lookup_code,'VENDOR TYPE',201) type, aps.segment1 supplier_number, aps.vat_registration_num tax_registration_number, aps.end_date_active, max(assa.vendor_site_code) keep (dense_rank last order by assa.vendor_site_id) over (partition by nvl(assa.location_id,assa.vendor_id) &partition_by) site_code, max(assa.address_line1) keep (dense_rank last order by assa.vendor_site_id) over (partition by nvl(assa.location_id,assa.vendor_id) &partition_by) address_line1, max(assa.address_line2) keep (dense_rank last order by assa.vendor_site_id) over (partition by nvl(assa.location_id,assa.vendor_id) &partition_by) address_line2, max(assa.city) keep (dense_rank last order by assa.vendor_site_id) over (partition by nvl(assa.location_id,assa.vendor_id) &partition_by) city, max(assa.zip) keep (dense_rank last order by assa.vendor_site_id) over (partition by nvl(assa.location_id,assa.vendor_id) &partition_by) zip, max(ftv.territory_short_name) keep (dense_rank last order by assa.vendor_site_id) over (partition by nvl(assa.location_id,assa.vendor_id) &partition_by) country, max(att.name) keep (dense_rank last order by assa.vendor_site_id) over (partition by nvl(assa.location_id,assa.vendor_id) &partition_by) payment_terms, sum(case when aia.gl_date>=add_months(x.start_date,-24) and aia.gl_date<add_months(x.start_date,-12) then aia.invoice_amount end) over (partition by nvl(assa.location_id,assa.vendor_id),nvl(aia.invoice_currency_code,gl.currency_code) &partition_by) amount_fy_&fy_2, sum(case when aia.gl_date>=add_months(x.start_date,-12) and aia.gl_date<x.start_date then aia.invoice_amount end) over (partition by nvl(assa.location_id,assa.vendor_id),nvl(aia.invoice_currency_code,gl.currency_code) &partition_by) amount_fy_&fy_1, sum(case when aia.gl_date>=x.start_date and aia.gl_date<:as_of_date+1 then aia.invoice_amount end) over (partition by nvl(assa.location_id,assa.vendor_id),nvl(aia.invoice_currency_code,gl.currency_code) &partition_by) amount_fy_&fy, sum(case when aia.gl_date>=add_months(x.start_date,-24) then aia.invoice_amount end) over (partition by nvl(assa.location_id,assa.vendor_id),nvl(aia.invoice_currency_code,gl.currency_code) &partition_by) amount_3_fys, sum(case when aia.gl_date>=:as_of_date-365*10 then aia.invoice_amount end) over (partition by nvl(assa.location_id,assa.vendor_id),nvl(aia.invoice_currency_code,gl.currency_code) &partition_by) amount_10_years, sum(case when aia.gl_date>=:as_of_date-365*3 then aia.invoice_amount end) over (partition by nvl(assa.location_id,assa.vendor_id),nvl(aia.invoice_currency_code,gl.currency_code) &partition_by) amount_3_years, sum(case when aia.gl_date>=:as_of_date-365*2 then aia.invoice_amount end) over (partition by nvl(assa.location_id,assa.vendor_id),nvl(aia.invoice_currency_code,gl.currency_code) &partition_by) amount_2_years, sum(case when aia.gl_date>=:as_of_date-365 then aia.invoice_amount end) over (partition by nvl(assa.location_id,assa.vendor_id),nvl(aia.invoice_currency_code,gl.currency_code) &partition_by) amount_1_year, sum(aia.invoice_amount) over (partition by nvl(assa.location_id,assa.vendor_id),nvl(aia.invoice_currency_code,gl.currency_code) &partition_by) amount_total, nvl(aia.invoice_currency_code,gl.currency_code) currency, ( select distinct listagg(ieba.masked_iban,',') within group (order by ieba.masked_iban) from (select iepa.* from iby_external_payees_all iepa where iepa.party_site_id is null and iepa.supplier_site_id is null) iepa, (select ipiua.* from iby_pmt_instr_uses_all ipiua where ipiua.payment_flow='DISBURSEMENTS' and sysdate between ipiua.start_date and nvl(ipiua.end_date,sysdate)) ipiua, iby_ext_bank_accounts ieba where aps.party_id=iepa.payee_party_id and iepa.payment_function='PAYABLES_DISB' and iepa.ext_payee_id=ipiua.ext_pmt_party_id and ipiua.order_of_preference=1 and ipiua.instrument_id=ieba.ext_bank_account_id ) iban_prio1, ( select distinct listagg(ieba.masked_iban,',') within group (order by ieba.masked_iban) from (select iepa.* from iby_external_payees_all iepa where iepa.party_site_id is null and iepa.supplier_site_id is null) iepa, (select ipiua.* from iby_pmt_instr_uses_all ipiua where ipiua.payment_flow='DISBURSEMENTS' and sysdate between ipiua.start_date and nvl(ipiua.end_date,sysdate)) ipiua, iby_ext_bank_accounts ieba where aps.party_id=iepa.payee_party_id and iepa.payment_function='PAYABLES_DISB' and iepa.ext_payee_id=ipiua.ext_pmt_party_id and ipiua.order_of_preference=2 and ipiua.instrument_id=ieba.ext_bank_account_id ) iban_prio2, ( select distinct listagg(ieba.masked_iban,',') within group (order by ieba.masked_iban) from (select iepa.* from iby_external_payees_all iepa where iepa.party_site_id is null and iepa.supplier_site_id is null) iepa, (select ipiua.* from iby_pmt_instr_uses_all ipiua where ipiua.payment_flow='DISBURSEMENTS' and sysdate between ipiua.start_date and nvl(ipiua.end_date,sysdate)) ipiua, iby_ext_bank_accounts ieba where aps.party_id=iepa.payee_party_id and iepa.payment_function='PAYABLES_DISB' and iepa.ext_payee_id=ipiua.ext_pmt_party_id and ipiua.order_of_preference=3 and ipiua.instrument_id=ieba.ext_bank_account_id ) iban_prio3, ( select distinct listagg(ieba.masked_iban,',') within group (order by ieba.masked_iban) from (select iepa.* from iby_external_payees_all iepa where iepa.party_site_id is null and iepa.supplier_site_id is null) iepa, (select ipiua.* from iby_pmt_instr_uses_all ipiua where ipiua.payment_flow='DISBURSEMENTS' and sysdate between ipiua.start_date and nvl(ipiua.end_date,sysdate)) ipiua, iby_ext_bank_accounts ieba where aps.party_id=iepa.payee_party_id and iepa.payment_function='PAYABLES_DISB' and iepa.ext_payee_id=ipiua.ext_pmt_party_id and ipiua.order_of_preference=4 and ipiua.instrument_id=ieba.ext_bank_account_id ) iban_prio4 from ap_suppliers aps, ap_supplier_sites_all assa, fnd_territories_vl ftv, ap_terms_tl att, ap_invoices_all aia, hr_operating_units hou, gl_ledgers gl, x where 1=1 and aps.vendor_id=assa.vendor_id and assa.country=ftv.territory_code(+) and assa.terms_id=att.term_id(+) and att.language(+)=userenv('lang') and assa.vendor_site_id=aia.vendor_site_id and assa.org_id=hou.organization_id and hou.set_of_books_id=gl.ledger_id #,##0.00;[Red]-#,##0.00 (:Ledger is not null or :Operating_Unit is not null) AR AP Suppliers Revenue Summary ملخص إيرادات موردي AP (بائعو PO) عبر وحدات تشغيل مختلفة D AP Suppliers Revenue Summary AP-Lieferanten (Po-Lieferanten) Umsatzübersicht über verschiedene Betriebseinheiten E AP Suppliers Revenue Summary Resumen de los ingresos de los proveedores de AP (po vendors) en las diferentes unidades operativas F AP Suppliers Revenue Summary Résumé des recettes des fournisseurs de PA (vendeurs de PA) dans les différentes unités opérationnelles I AP Suppliers Revenue Summary Riepilogo delle entrate dei fornitori AP (venditori di po) nelle diverse unità operative JA AP Suppliers Revenue Summary APサプライヤー（POベンダー）の異なる事業単位での収益サマリー KO AP Suppliers Revenue Summary 여러 운영 단위의 AP 공급자 (PO 공급 업체) 수익 요약 PTB AP Suppliers Revenue Summary Resumo da receita de fornecedores AP (po vendors) em diferentes unidades operacionais RU AP Suppliers Revenue Summary Сводка доходов поставщиков AP (Po поставщиков) по различным операционным подразделениям S AP Suppliers Revenue Summary AP-leverantörer (po-leverantörer) intäktsöversikt över olika operativa enheter TR AP Suppliers Revenue Summary Farklı işletim birimlerindeki AP tedarikçileri (po satıcılar) gelir özeti US AP Suppliers Revenue Summary AP suppliers (po vendors) revenue summary across different operating units Please enter a Ledger or Operating Unit ZHS AP Suppliers Revenue Summary 不同经营单位的亚太供应商（宝商）收入汇总 Enginatics &fy &fy_1 &fy_2 &lp_columns &partition_by 1=1 :as_of_date 1 10 1=1 gl.name=:ledger and hou.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) LOV GL Ledger 8E2FF36EDEB879D2E0530100007F1FF2 Y select gl.name value, fifsv.id_flex_structure_name||': '||decode(gl.ledger_category_code,'NONE',xxen_util.meaning(gl.object_type_code,'LEDGERS',101),xxen_util.meaning(gl.ledger_category_code,'GL_ASF_LEDGER_CATEGORY',101))||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where (:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y')) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') and gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+)) and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by fifsv.id_flex_structure_name, decode(gl.ledger_category_code,'PRIMARY',1,'SECONDARY',2,'ALC',3,'NONE',4), gl.name coalesce(xxen_util.previous_parameter_value(:parameter_id),xxen_util.default_ledger) F Grand livre US Ledger 2 20 1=1 hou.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 mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) order by hou.name F Unité d'exploitation US Operating Unit 3 30 1=1 aps.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 4 40 1=1 aps.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 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 :as_of_date Date sysdate F En date du US As of Date 7 &fy select gp.period_year from gl_periods gp where :as_of_date between gp.start_date and gp.end_date and (gp.period_set_name, gp.period_type) in (select gl.period_set_name, gl.accounted_period_type from gl_ledgers gl where gl.object_type_code='L' and gl.ledger_category_code='PRIMARY' and rownum=1) and gp.adjustment_period_flag='N' F En date du US As of Date 8 &fy_1 select gp.period_year-1 from gl_periods gp where :as_of_date between gp.start_date and gp.end_date and (gp.period_set_name, gp.period_type) in (select gl.period_set_name, gl.accounted_period_type from gl_ledgers gl where gl.object_type_code='L' and gl.ledger_category_code='PRIMARY' and rownum=1) and gp.adjustment_period_flag='N' F En date du US As of Date 9 &fy_2 select gp.period_year-2 from gl_periods gp where :as_of_date between gp.start_date and gp.end_date and (gp.period_set_name, gp.period_type) in (select gl.period_set_name, gl.accounted_period_type from gl_ledgers gl where gl.object_type_code='L' and gl.ledger_category_code='PRIMARY' and rownum=1) and gp.adjustment_period_flag='N' F En date du US As of Date 10 70 &partition_by , gl.name LOV custom Y select 'Global' value, null description from dual union all select 'Ledger' value, null description from dual union all select 'Operating Unit' value, null description from dual Ledger Global Y F Niveau résumé US Summary Level 11 &partition_by , gl.name, hou.name Operating Unit F Niveau résumé US Summary Level 12 &lp_columns gl.name ledger, Ledger F Niveau résumé US Summary Level 13 &lp_columns gl.name ledger, hou.name operating_unit, Operating Unit F Niveau résumé US Summary Level