ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4709651A9E05362FB09050512Y 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 = 'A' 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 = 'A' 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) JG JGZZAPTE_NTRL_XMLP AR JG ضريبة القيمة المضافة لـ EMEA: استخراج بيانات معدل دوران حساب المدفوعات بدون TRL Imported from BI Publisher Description: استخراج بيانات معدل دوران حساب المدفوعات بدون TRL Application: المحليات الأقليمية Source: ضريبة القيمة المضافة لـ EMEA: استخراج بيانات معدل دوران حساب المدفوعات بدون TRL Short Name: JGZZAPTE_NTRL_XMLP DB package: JG_ZZ_PTCE_DT_PKG D JG EMEA USt: KR-Umsatz - Datenauszug ohne Steuerauswertungsbuch Imported from BI Publisher Description: KR-Umsatz - Datenauszug ohne Steuerauswertungsbuch Application: Regionale Lokalisierungen Source: EMEA USt: KR-Umsatz - Datenauszug ohne Steuerauswertungsbuch Short Name: JGZZAPTE_NTRL_XMLP DB package: JG_ZZ_PTCE_DT_PKG E JG EMEA - I.V.A.: Extracto de Facturación Total de AP fuera del Libro Mayor de Información Fiscal Imported from BI Publisher Description: Extracto de facturación total de AP fuera del libro mayor de información fiscal Application: Localizaciones Regionales Source: EMEA - I.V.A.: Extracto de Facturación Total de AP fuera del Libro Mayor de Información Fiscal Short Name: JGZZAPTE_NTRL_XMLP DB package: JG_ZZ_PTCE_DT_PKG F JG EMEA - TVA : Chiffre d'affaires - AP - Hors livre de déclaration fiscale : Extraction Imported from BI Publisher Description: Chiffre d'affaires - AP - Hors livre de déclaration fiscale : Extraction Application: Regional Localizations Source: EMEA - TVA : Chiffre d'affaires - AP - Hors livre de déclaration fiscale : Extraction Short Name: JGZZAPTE_NTRL_XMLP DB package: JG_ZZ_PTCE_DT_PKG I JG IVA EMEA: estrazione fatturato AP non TRL Imported from BI Publisher Description: Estrazione non TRL AP fatturato Application: Regional Localizations Source: IVA EMEA: estrazione fatturato AP non TRL Short Name: JGZZAPTE_NTRL_XMLP DB package: JG_ZZ_PTCE_DT_PKG JA JG EMEA VAT: 売上AP非TRL抽出 Imported from BI Publisher Description: 売上AP非TRL抽出 Application: Regional Localizations Source: EMEA VAT: 売上AP非TRL抽出 Short Name: JGZZAPTE_NTRL_XMLP DB package: JG_ZZ_PTCE_DT_PKG KO JG EMEA VAT: AP 비세금 보고 원장 회전율 추출 Imported from BI Publisher Description: AP 비세금 보고 원장 회전율 추출 Application: Regional Localizations Source: EMEA VAT: AP 비세금 보고 원장 회전율 추출 Short Name: JGZZAPTE_NTRL_XMLP DB package: JG_ZZ_PTCE_DT_PKG PTB JG IVA do EMEA: Extração Não TRL do AP de Movimentação Imported from BI Publisher Description: Extração Não TRL do AP de Movimentação Application: Locais Regionais Source: IVA do EMEA: Extração Não TRL do AP de Movimentação Short Name: JGZZAPTE_NTRL_XMLP DB package: JG_ZZ_PTCE_DT_PKG RU JG НДС (EMEA): извлечение "Оборот - КР без КНО" Imported from BI Publisher Description: Извлечение "Оборот - КР без КНО" Application: Местные версии Source: НДС (EMEA): извлечение "Оборот - КР без КНО" Short Name: JGZZAPTE_NTRL_XMLP DB package: JG_ZZ_PTCE_DT_PKG S JG EMEA - moms: Extrahering av omsättning för leverantörsreskontra som inte är skatterapp.reskontra Imported from BI Publisher Description: Extrahering av omsättning för leverantörsreskontra som inte är skatterapp.reskontra Application: Regional Localizations Source: EMEA - moms: Extrahering av omsättning för leverantörsreskontra som inte är skatterapp.reskontra Short Name: JGZZAPTE_NTRL_XMLP DB package: JG_ZZ_PTCE_DT_PKG TR JG EMEA KDV: Ciro AP TRL Dışı Çıkarma Imported from BI Publisher Description: Ciro AP TRL Dışı Çıkarma Application: Bölgesel Yerelleştirmeler Source: EMEA KDV: Ciro AP TRL Dışı Çıkarma Short Name: JGZZAPTE_NTRL_XMLP DB package: JG_ZZ_PTCE_DT_PKG US JG EMEA VAT: Turnover AP Non TRL Extract Imported from BI Publisher Description: Turnover AP Non TRL Extract Application: Regional Localizations Source: EMEA VAT: Turnover AP Non TRL Extract Short Name: JGZZAPTE_NTRL_XMLP DB package: JG_ZZ_PTCE_DT_PKG ZHS JG EMEA 增值税：营业税 AP 非 TRL 提取 Imported from BI Publisher Description: 营业税 AP 非 TRL 提取 Application: 区域性本地化 Source: EMEA 增值税：营业税 AP 非 TRL 提取 Short Name: JGZZAPTE_NTRL_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 JGZZPTCE 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 JA コール・レポート コール・レポート KO 보고서 호출 보고서 호출 PTB Relatório de Chamada Relatório de Chamada RU Отчет вызова Отчет вызова S Anropar rapport Anropar rapport TR Çağrı Raporu Çağrı Raporu US Calling Report Calling Report ZHS 调用报表 调用报表 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 Informes Identificador de informes F Identifiant de reporting Identifiant de reporting I Identificativo dichiarazione Identificativo dichiarazione JA レポート識別子 レポート識別子 KO 보고 식별자 보고 식별자 PTB Identificador de Relatório Identificador de Relatório RU Идентификатор отчета Идентификатор отчета S Rapporterings-id Rapporterings-id TR Raporlama Tanımlayıcı Raporlama Tanımlayıcı US Reporting Identifier Reporting Identifier ZHS 申报标识 申报标识 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 JA 会計年度 会計年度 KO 회계년도 회계년도 PTB Ano Fiscal Ano Fiscal RU Финансовый год Финансовый год S Räkenskapsår Räkenskapsår TR Mali Yıl Mali Yıl US Fiscal Year Fiscal Year ZHS 会计年度 会计年度