ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4708451A9E05362FB09050512Y SELECT curr_inv_flag, sum(inv_vendor_id) inv_vendor_id, sum(period_year) period_year, sum(count_distinct_ih_doc_sequence) count_distinct_ih_doc_sequence, sum(taxable_amt) taxable_amt, sum(tax_amt) tax_amt, sum(exempt_amt) exempt_amt, sum(nt_amt) nt_amt, sum(total_amt) total_amt FROM( SELECT 'Y' curr_inv_flag, jgzzvgt.jg_info_n2 inv_vendor_id, /* vendor_id */ gp.period_year, count(distinct(jgzzvgt.jg_info_n1)) count_distinct_ih_doc_sequence, /* invoice_id */ sum( decode(DECODE(ztb.offset_tax_flag , 'Y', 'OFFSET', DECODE(zrb.def_rec_settlement_option_code, 'DEFFERED','DEFFERED', zxass.REPORTING_CODE_CHAR_VALUE)), 'EXEMPT',0, decode(DECODE(ztb.offset_tax_flag , 'Y', 'OFFSET', DECODE(zrb.def_rec_settlement_option_code, 'DEFFERED','DEFFERED', zxass.REPORTING_CODE_CHAR_VALUE)),'NON TAXABLE',0, decode(DECODE(ztb.offset_tax_flag , 'Y', 'OFFSET', DECODE(zrb.def_rec_settlement_option_code, 'DEFFERED','DEFFERED', zxass.REPORTING_CODE_CHAR_VALUE)),'SELF INVOICE',nvl(jgzzvgt.jg_info_n8,0) /* stat_amount */, decode(jgzzvgt.jg_info_v3 /* line_type_lookup_code */, 'TAX', 0, decode(jgzzvgt.jg_info_v1 /* invoice_currency_code */, JG_ZZ_PTCE_DT_PKG.get_gv_currency_code, jgzzvgt.jg_info_n9 /* amount */, jgzzvgt.jg_info_n10 /* base_amount */)))))) taxable_amt, sum(decode(DECODE(ztb.offset_tax_flag , 'Y', 'OFFSET', DECODE(zrb.def_rec_settlement_option_code, 'DEFFERED','DEFFERED', zxass.REPORTING_CODE_CHAR_VALUE)),'EXEMPT',0, decode(DECODE(ztb.offset_tax_flag , 'Y', 'OFFSET', DECODE(zrb.def_rec_settlement_option_code, 'DEFFERED','DEFFERED', zxass.REPORTING_CODE_CHAR_VALUE)), 'NON TAXABLE', 0, decode(jgzzvgt.jg_info_v3 /* line_type_lookup_code */, 'TAX', decode(jgzzvgt.jg_info_v1 /* invoice_currency_code */, JG_ZZ_PTCE_DT_PKG.get_gv_currency_code, jgzzvgt.jg_info_n9 /* amount */, jgzzvgt.jg_info_n10),0 /* base_amount */)))) tax_amt, sum(decode(DECODE(ztb.offset_tax_flag , 'Y', 'OFFSET', DECODE(zrb.def_rec_settlement_option_code, 'DEFFERED','DEFFERED', zxass.REPORTING_CODE_CHAR_VALUE)),'EXEMPT', decode(jgzzvgt.jg_info_v3 /* line_type_lookup_code */, 'TAX', 0, decode(jgzzvgt.jg_info_v1 /* invoice_currency_code */,JG_ZZ_PTCE_DT_PKG.get_gv_currency_code, jgzzvgt.jg_info_n9 /* amount */, jgzzvgt.jg_info_n10 /* base_amount */)),0 )) exempt_amt, sum(decode(DECODE(ztb.offset_tax_flag , 'Y', 'OFFSET', DECODE(zrb.def_rec_settlement_option_code, 'DEFFERED','DEFFERED', zxass.REPORTING_CODE_CHAR_VALUE)), 'NON TAXABLE', decode(jgzzvgt.jg_info_v3 /* line_type_lookup_code */, 'TAX', 0, decode(jgzzvgt.jg_info_v1 /* invoice_currency_code */, JG_ZZ_PTCE_DT_PKG.get_gv_currency_code, jgzzvgt.jg_info_n9 /* amount */, jgzzvgt.jg_info_n10 /* base_amount */)),0)) nt_amt, sum(decode(DECODE(ztb.offset_tax_flag , 'Y', 'OFFSET', DECODE(zrb.def_rec_settlement_option_code, 'DEFFERED','DEFFERED', zxass.REPORTING_CODE_CHAR_VALUE)),'SELF INVOICE',nvl(jgzzvgt.jg_info_n8,0) /* stat_amount */ , decode(jgzzvgt.jg_info_v1 /* invoice_currency_code */, JG_ZZ_PTCE_DT_PKG.get_gv_currency_code, jgzzvgt.jg_info_n9 /* amount */, jgzzvgt.jg_info_n10 /* base_amount */))) total_amt FROM jg_zz_vat_trx_gt jgzzvgt , zx_rates_b zrb, zx_taxes_b ztb, zx_report_codes_assoc zxass, gl_periods gp, jg_zz_vat_rep_entities cfg WHERE :P_called_from = 'JEITRAVL' and cfg.vat_reporting_entity_id = :p_reporting_entity_id and jgzzvgt.jg_info_n2 = :vendor_id_sd and jgzzvgt.jg_info_n7 = zrb.tax_rate_id and zrb.tax_regime_code = ztb.tax_regime_code and zrb.tax = ztb.tax and zrb.content_owner_id = ztb.content_owner_id and zrb.tax_rate_id = zxass.entity_id(+) and zxass.entity_code(+) = 'ZX_RATES' and jgzzvgt.jg_info_v5 in ('APPROVED','CANCELLED','FULL','AVAILABLE') and jgzzvgt.jg_info_v4 = gp.period_name and gp.period_set_name = cfg.tax_calendar_name and gp.period_year = to_number(to_char(JG_ZZ_PTCE_DT_PKG.get_gd_period_end_date, 'YYYY')) and to_char(jgzzvgt.jg_info_d1, 'YYYY') = to_char(JG_ZZ_PTCE_DT_PKG.get_gd_period_end_date, 'YYYY') and DECODE(ztb.offset_tax_flag , 'Y', 'OFFSET', DECODE(zrb.def_rec_settlement_option_code, 'DEFFERED','DEFFERED', zxass.REPORTING_CODE_CHAR_VALUE)) != 'CUSTOM BILL' group by jgzzvgt.jg_info_n2, /* vendor_id */ gp.period_year UNION SELECT 'Y' curr_inv_flag, 0 inv_vendor_id, /* vendor_id */ 0 period_year, 0 count_distinct_ih_doc_sequence, /* invoice_id */ 0 taxable_amt, 0 tax_amt, 0 exempt_amt, 0 nt_amt, 0 total_amt FROM dual UNION SELECT 'N' curr_inv_flag, jgzzvgt.jg_info_n2 inv_vendor_id, /* vendor_id */ gp.period_year, count(distinct(jgzzvgt.jg_info_n1)) count_distinct_ih_doc_sequence, /* invoice_id */ sum( decode(DECODE(ztb.offset_tax_flag , 'Y', 'OFFSET', DECODE(zrb.def_rec_settlement_option_code, 'DEFFERED','DEFFERED', zxass.REPORTING_CODE_CHAR_VALUE)), 'EXEMPT',0, decode(DECODE(ztb.offset_tax_flag , 'Y', 'OFFSET', DECODE(zrb.def_rec_settlement_option_code, 'DEFFERED','DEFFERED', zxass.REPORTING_CODE_CHAR_VALUE)),'NON TAXABLE',0, decode(DECODE(ztb.offset_tax_flag , 'Y', 'OFFSET', DECODE(zrb.def_rec_settlement_option_code, 'DEFFERED','DEFFERED', zxass.REPORTING_CODE_CHAR_VALUE)),'SELF INVOICE',nvl(jgzzvgt.jg_info_n8,0) /* stat_amount */, decode(jgzzvgt.jg_info_v3 /* line_type_lookup_code */ , 'TAX', 0, decode(jgzzvgt.jg_info_v1 /* invoice_currency_code */, JG_ZZ_PTCE_DT_PKG.get_gv_currency_code, jgzzvgt.jg_info_n9 /* amount */ ,jgzzvgt.jg_info_n10 /* base_amount */ )))))) taxable_amt, sum(decode(DECODE(ztb.offset_tax_flag , 'Y', 'OFFSET', DECODE(zrb.def_rec_settlement_option_code, 'DEFFERED','DEFFERED', zxass.REPORTING_CODE_CHAR_VALUE)),'EXEMPT',0, decode(DECODE(ztb.offset_tax_flag , 'Y', 'OFFSET', DECODE(zrb.def_rec_settlement_option_code, 'DEFFERED','DEFFERED', zxass.REPORTING_CODE_CHAR_VALUE)), 'NON TAXABLE', 0, decode(jgzzvgt.jg_info_v3 /* line_type_lookup_code */ , 'TAX', decode(jgzzvgt.jg_info_v1 /* invoice_currency_code */ , JG_ZZ_PTCE_DT_PKG.get_gv_currency_code, jgzzvgt.jg_info_n9 /* amount */ , jgzzvgt.jg_info_n10 /* base_amount */ ),0)))) tax_amt, sum(decode(DECODE(ztb.offset_tax_flag , 'Y', 'OFFSET', DECODE(zrb.def_rec_settlement_option_code, 'DEFFERED','DEFFERED', zxass.REPORTING_CODE_CHAR_VALUE)),'EXEMPT', decode(jgzzvgt.jg_info_v3 /* line_type_lookup_code */ , 'TAX', 0, decode(jgzzvgt.jg_info_v1 /* invoice_currency_code */ , JG_ZZ_PTCE_DT_PKG.get_gv_currency_code, jgzzvgt.jg_info_n9 /* amount */ ,jgzzvgt.jg_info_n10 /* base_amount */ )),0)) exempt_amt, sum(decode(DECODE(ztb.offset_tax_flag , 'Y', 'OFFSET', DECODE(zrb.def_rec_settlement_option_code, 'DEFFERED','DEFFERED', zxass.REPORTING_CODE_CHAR_VALUE)), 'NON TAXABLE', decode(jgzzvgt.jg_info_v3 /* line_type_lookup_code */ , 'TAX', 0, decode(jgzzvgt.jg_info_v1 /* invoice_currency_code */ , JG_ZZ_PTCE_DT_PKG.get_gv_currency_code, jgzzvgt.jg_info_n9 /* amount */ ,jgzzvgt.jg_info_n10 /* base_amount */ )),0)) nt_amt, sum(decode(DECODE(ztb.offset_tax_flag , 'Y', 'OFFSET', DECODE(zrb.def_rec_settlement_option_code, 'DEFFERED','DEFFERED', zxass.REPORTING_CODE_CHAR_VALUE)),'SELF INVOICE',nvl(jgzzvgt.jg_info_n8,0) /* stat_amount */ , decode(jgzzvgt.jg_info_v1 /* invoice_currency_code */ , JG_ZZ_PTCE_DT_PKG.get_gv_currency_code, jgzzvgt.jg_info_n9 /* amount */ , jgzzvgt.jg_info_n10 /* base_amount */ ))) total_amt FROM jg_zz_vat_trx_gt jgzzvgt , zx_rates_b zrb, zx_taxes_b ztb, zx_report_codes_assoc zxass, gl_periods gp, jg_zz_vat_rep_entities cfg WHERE :P_called_from = 'JEITRAVL' and cfg.vat_reporting_entity_id = :p_reporting_entity_id and jgzzvgt.jg_info_n2 = :vendor_id_sd and jgzzvgt.jg_info_n7 = zrb.tax_rate_id and zrb.tax_regime_code = ztb.tax_regime_code and zrb.tax = ztb.tax and zrb.content_owner_id = ztb.content_owner_id and zrb.tax_rate_id = zxass.entity_id(+) and zxass.entity_code(+) = 'ZX_RATES' and jgzzvgt.jg_info_v5 in ('APPROVED','CANCELLED','FULL','AVAILABLE') and jgzzvgt.jg_info_v4 = gp.period_name and gp.period_set_name = cfg.tax_calendar_name and gp.period_year = to_number(to_char(JG_ZZ_PTCE_DT_PKG.get_gd_period_end_date, 'YYYY')) and to_char(jgzzvgt.jg_info_d1, 'YYYY') = to_char(add_months(JG_ZZ_PTCE_DT_PKG.get_gd_period_end_date,-12), 'YYYY') and DECODE(ztb.offset_tax_flag , 'Y', 'OFFSET', DECODE(zrb.def_rec_settlement_option_code, 'DEFFERED','DEFFERED', zxass.REPORTING_CODE_CHAR_VALUE)) != 'CUSTOM BILL' group by jgzzvgt.jg_info_n2, /* vendor_id */ gp.period_year UNION SELECT 'N' curr_inv_flag, 0 inv_vendor_id, /* vendor_id */ 0 period_year, 0 count_distinct_ih_doc_sequence, /* invoice_id */ 0 taxable_amt, 0 tax_amt, 0 exempt_amt, 0 nt_amt, 0 total_amt FROM dual) GROUP BY CURR_INV_FLAG ORDER BY DECODE(CURR_INV_FLAG, 'Y', 1, 'N', 2) JE JEPTAPVR_XMLP AR JE تقرير إجمالي الموردين البرتغاليين Imported from BI Publisher Application: المحليات الأوروبية Source: تقرير إجمالي الموردين البرتغاليين Short Name: JEPTAPVR_XMLP DB package: JG_ZZ_PTCE_DT_PKG D JE Lieferanten - USt-Übersicht Imported from BI Publisher Description: Portugal: Auswertung "Lieferanten - USt-Übersicht" Application: Europäische Lokalisierungen Source: Portugal: Lieferanten - USt-Übersicht Short Name: JEPTAPVR_XMLP DB package: JG_ZZ_PTCE_DT_PKG E JE Portugal - Informe Recapitulativo de Proveedores Imported from BI Publisher Application: Localizaciones Europeas Source: Portugal - Informe Recapitulativo de Proveedores Short Name: JEPTAPVR_XMLP DB package: JG_ZZ_PTCE_DT_PKG F JE Portugal - Etat récapitulatif des fournisseurs Imported from BI Publisher Application: European Localizations Source: Portugal - Etat récapitulatif des fournisseurs Short Name: JEPTAPVR_XMLP DB package: JG_ZZ_PTCE_DT_PKG I JE Portogallo - rapporto Riepilogo fornitori Imported from BI Publisher Application: European Localizations Source: Portogallo - rapporto Riepilogo fornitori Short Name: JEPTAPVR_XMLP DB package: JG_ZZ_PTCE_DT_PKG RU JE Краткий отчет о поставщиках (Португалия) Imported from BI Publisher Application: Версии для Европы Source: Краткий отчет о поставщиках (Португалия) Short Name: JEPTAPVR_XMLP DB package: JG_ZZ_PTCE_DT_PKG S JE Sammanfattande rapport över leverantörer Imported from BI Publisher Application: European Localizations Source: Portugisiska: Sammanfattande rapport över leverantörer Short Name: JEPTAPVR_XMLP DB package: JG_ZZ_PTCE_DT_PKG TR JE Portuguese Suppliers Recapitulative Imported from BI Publisher Application: Avrupa Yerelleştirmeleri Source: Portuguese Suppliers Recapitulative Report Short Name: JEPTAPVR_XMLP DB package: JG_ZZ_PTCE_DT_PKG US JE Portuguese Suppliers Recapitulative Imported from BI Publisher Application: European Localizations Source: Portuguese Suppliers Recapitulative Report Short Name: JEPTAPVR_XMLP DB package: JG_ZZ_PTCE_DT_PKG BI Publisher :gd_period_end_date :gd_period_start_date :gn_legal_entity_id :gn_thousands :gv_balancing_segment_value :gv_chart_of_accounts_id :gv_country :gv_currency_code :gv_entity_identifier :gv_ledger_id :gv_name :gv_repent_address_line_1 :gv_repent_address_line_2 :gv_repent_address_line_3 :gv_repent_country :gv_repent_id_number :gv_repent_name :gv_repent_phone_number :gv_repent_postal_code :gv_repent_town_or_city :gv_repent_trn :gv_tax_office_code :gv_tax_office_location :gv_tax_office_number :gv_tax_reg_num :gv_vat_country_code :gv_vat_reg_num :p_called_from :p_conc_request_id :p_dec_type :p_fiscal_year :p_min_inv_amt :p_reporting_entity_id :vendor_id_sd 1 -10 :p_called_from Char JEPTAPVR AR طلب التقرير طلب التقرير D Aufrufende Auswertung Aufrufende Auswertung E Informe Que Solicita los Datos Informe Que Solicita los Datos F Etat appelant Etat appelant I Rapporto chiamante Rapporto chiamante RU Отчет вызова Отчет вызова S Anropar rapport Anropar rapport US Calling Report Calling Report 2 10 :p_reporting_entity_id LOV Oracle JGZZ_VAT_REPORTING_ENTITY_ID Y select vat_reporting_entity_id id, entity_identifier value, null description from jg_zz_vat_rep_entities order by value,description Y AR معرف إعداد التقارير معرف إعداد التقارير D Kennung Steuerpflichtiger Kennung Steuerpflichtiger E Identificador de Entidad Fiscal Identificador de Entidad Fiscal F Identifiant d'entité déclarante Identifiant d'entité déclarante I Identificativo soggetto fiscale Identificativo soggetto fiscale RU Идентификатор отчета Идентификатор отчета S Rapporterings-id Rapporterings-id TR Raporlama Tanımlayıcısı Raporlama Tanımlayıcısı US Reporting Identifier Reporting Identifier 3 20 :p_fiscal_year LOV Oracle JGZZ_VAT_FISCAL_YEAR Y select glp.period_year id, glp.period_year value, null description from (select distinct(period_year),period_set_name from gl_periods) glp,jg_zz_vat_rep_entities cfgs where glp.period_set_name=cfgs.tax_calendar_name and cfgs.vat_reporting_entity_id= (select b.vat_reporting_entity_id from jg_zz_vat_rep_entities a,jg_zz_vat_rep_entities b where a.vat_reporting_entity_id=:$flex$.jgzz_vat_reporting_entity_id and ((a.entity_type_code='ACCOUNTING' and a.mapping_vat_rep_entity_id=b.vat_reporting_entity_id ) or (a.entity_type_code='LEGAL' and a.vat_reporting_entity_id=b.vat_reporting_entity_id ) )) order by value,description Y AR السنة المالية السنة المالية D Geschäftsjahr Geschäftsjahr E Año Fiscal Año Fiscal F Exercice Exercice I Anno fiscale Anno fiscale RU Финансовый год Финансовый год S Räkenskapsår Räkenskapsår TR Mali Yıl Mali Yıl US Fiscal Year Fiscal Year 4 30 :p_dec_type LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description select meaning from fnd_lookups where lookup_type = 'YES_NO' and lookup_code = 'Y' Y AR نوع أول إقرار نوع أول إقرار D Von Meldeart Von Meldeart E Primer Tipo de Declaración Primer Tipo de Declaración F Premier type de déclaration Premier type de déclaration I Primo tipo di dichiarazione Primo tipo di dichiarazione RU Тип первой декларации Тип первой декларации S Första deklarationstypen Första deklarationstypen US First Declaration Type First Declaration Type 5 40 :p_min_inv_amt Number 0 Y AR الحد الأدنى للمبلغ المحرر له فاتورة الحد الأدنى للمبلغ المحرر له فاتورة D Mindestbetrag Rechnung Mindestbetrag Rechnung E Importe Mínimo Facturado Importe Mínimo Facturado F Montant minimum facturé Montant minimum facturé I Importo minimo fatturato Importo minimo fatturato RU Минимальная сумма по счету-фактуре Минимальная сумма по счету-фактуре S Lägsta fakturerade belopp Lägsta fakturerade belopp TR Minimum Faturalanan Tutar Minimum Faturalanan Tutar US Minimum Invoiced Amount Minimum Invoiced Amount :$flex$.jgzz_vat_reporting_entity_id Reporting Identifier Fiscal Year