ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4705951A9E05362FB09050512Y SELECT JG.accounting_date ACCOUNTING_DATE ,JG.doc_seq_name ||'/'|| JG.doc_seq_value TRX_DOC_SEQUENCE_VALUE ,JG.trx_date TRX_DATE ,JG.trx_number TRX_NUMBER ,JG.billing_tp_name BILLING_TP_NAME ,JG.billing_tp_taxpayer_id BILLING_TP_TAXPAYER_ID ,SUM((NVL(JG.taxable_amt, JG.taxable_amt_funcl_curr))*(NVL(TAX_RECOVERY_RATE,0)/100)) NET_AMOUNT_ORIG -- Modified for bug 7457763 ,JG.tax_rate TAX_RATE ,JG.tax_rate_code TAX_CODE ,JG.tax_rate_code_description TAX_DESCRIPTION ,SUM(NVL(JG.tax_amt,JG.tax_amt_funcl_curr)) TAX_AMOUNT_ORIG ,JG.trx_line_class INVOICE_TYPE ,HZL.address1||' '||HZL.address2||' '||HZL.address3 C_DIRECC ,HZL.postal_code||' '||HZL.city C_CIUDAD ,HZL.country BILLING_TP_COUNTRY ,JG.territory_short_name TERRITORY_NAME ,JG.billing_tp_tax_reg_num BILLING_TP_TAX_REG_NUM ,JG.reporting_code OFFSET_TAX_CODE_ID FROM jg_zz_vat_trx_details JG ,jg_zz_vat_rep_status JZVRS ,hz_locations HZL ,hz_party_sites HPS WHERE JG.reporting_status_id in (SELECT DISTINCT JZRS.reporting_status_id JZRS FROM jg_zz_vat_rep_status JZRS WHERE JZRS.vat_reporting_entity_id = :P_VAT_REP_ENTITY_ID AND JZRS.source = 'AP') --JZVRS.reporting_status_id = JG.reporting_status_id AND JZVRS.vat_reporting_entity_id = :P_VAT_REP_ENTITY_ID AND JG.billing_tp_site_id = HPS.party_site_id (+) --brathod, changed to outer join AND HPS.location_id = HZL.location_id (+) --brathod, changed to outer join AND JZVRS.tax_calendar_period IN (SELECT RPS1.tax_calendar_period FROM JG_ZZ_VAT_REP_STATUS RPS1, (Select min(vat_reporting_entity_id) vat_reporting_entity_id, min(period_start_date) period_start_date From JG_ZZ_VAT_REP_STATUS Where vat_reporting_entity_id = :P_VAT_REP_ENTITY_ID And tax_calendar_period =:P_PERIOD_NAME) RPS2, (Select min(vat_reporting_entity_id) vat_reporting_entity_id, min(period_end_date) period_end_date From JG_ZZ_VAT_REP_STATUS Where vat_reporting_entity_id = :P_VAT_REP_ENTITY_ID And tax_calendar_period = :P_PERIOD_NAME_TO) RPS3 WHERE RPS1.vat_reporting_entity_id = :P_VAT_REP_ENTITY_ID AND RPS2.vat_reporting_entity_id = RPS1.vat_reporting_entity_id AND RPS3.vat_reporting_entity_id = RPS2.vat_reporting_entity_id AND trunc(RPS1.period_start_date) >= trunc(RPS2.period_start_date) AND trunc(RPS1.period_end_date) <= trunc(RPS3.period_end_date) GROUP by RPS1.tax_calendar_period) AND JG.gl_date BETWEEN JZVRS.period_start_date AND JZVRS.period_end_date AND JZVRS.source = 'AP' AND (:P_BAL_SEGMENT IS NULL OR JG.trx_tax_balancing_segment = :P_BAL_SEGMENT) /* UT Bug#5308180 AND JG.tax_invoice_date BETWEEN JZVRS.period_start_date AND JZVRS.period_end_date */ AND nvl(JG.reporting_code, 'AZX') NOT LIKE 'OFFSET' AND ( JG.offset_tax_rate_code IS NOT NULL or JG.self_assessed_flag ='Y') /* UT Bug#5308180 AND JG.tax_type_code = NVL(:P_TAX_TYPE,JG.tax_type_code) */ AND (:P_TAX_TYPE is null or JG.reporting_code = :P_TAX_TYPE) AND :P_REPORT_NAME = 'JEESDOCE' AND JG.trx_id IN (SELECT JG1.trx_id FROM jg_zz_vat_trx_details JG1 WHERE ( JG1.reporting_code LIKE 'OFFSET' OR JG1.self_assessed_flag ='Y') AND JG.trx_id = JG1.trx_id AND JG.application_id = JG1.application_id AND JG.event_class_code = JG1.event_class_code AND JG.entity_code = JG1.entity_code) AND JG.extract_source_ledger ='AP' GROUP BY JG.accounting_date ,JG.doc_seq_name ||'/'|| JG.doc_seq_value ,JG.trx_date ,JG.trx_number ,JG.billing_tp_name ,JG.billing_tp_taxpayer_id ,JG.tax_rate ,JG.tax_rate_code ,JG.tax_rate_code_description ,JG.trx_line_class ,HZL.address1||' '||HZL.address2||' '||HZL.address3 ,HZL.postal_code||' '||HZL.city ,HZL.country ,JG.territory_short_name ,JG.billing_tp_tax_reg_num ,JG.reporting_code HAVING SUM((NVL(JG.taxable_amt, JG.taxable_amt_funcl_curr))*(NVL(TAX_RECOVERY_RATE,0)/100)) <> 0 ORDER BY JG.trx_date ,JG.trx_number JE JEESDOCE_XMLP AR JE صيغة الفاتورة الأسبانية داخل الاتحاد الأوروبي (مستند مكافئ) Imported from BI Publisher Application: المحليات الأوروبية Source: صيغة الفاتورة الأسبانية داخل الاتحاد الأوروبي (مستند مكافئ) Short Name: JEESDOCE_XMLP DB package: JG_ZZ_AUDIT_AP_PKG D JE Innergemeinschaftliches Rechnungsformat (Documento Equivalente) Imported from BI Publisher Application: Europäische Lokalisierungen Source: Spanien: Innergemeinschaftliches Rechnungsformat (Documento Equivalente) Short Name: JEESDOCE_XMLP DB package: JG_ZZ_AUDIT_AP_PKG E JE España - Formato de Facturas Intracomunitarias (Documento Equivalente) Imported from BI Publisher Application: Localizaciones Europeas Source: España - Formato de Facturas Intracomunitarias (Documento Equivalente) Short Name: JEESDOCE_XMLP DB package: JG_ZZ_AUDIT_AP_PKG F JE Espagne - Inter-UE - Format de facture (Documento Equivalente) Imported from BI Publisher Application: European Localizations Source: Espagne - Inter-UE - Format de facture (Documento Equivalente) Short Name: JEESDOCE_XMLP DB package: JG_ZZ_AUDIT_AP_PKG I JE Spagna - formato fattura intereuropeo (Documento Equivalente) Imported from BI Publisher Application: European Localizations Source: Spagna - formato fattura intereuropeo (Documento Equivalente) Short Name: JEESDOCE_XMLP DB package: JG_ZZ_AUDIT_AP_PKG RU JE Формат счета-фактуры для стран-членов ЕС (Испания - Documento Equivalente) Imported from BI Publisher Application: Версии для Европы Source: Формат счета-фактуры для стран-членов ЕС (Испания - Documento Equivalente) Short Name: JEESDOCE_XMLP DB package: JG_ZZ_AUDIT_AP_PKG S JE Spanskt fakturaformat inom EU (Documento Equivalente) Imported from BI Publisher Application: European Localizations Source: Spanskt fakturaformat inom EU (Documento Equivalente) Short Name: JEESDOCE_XMLP DB package: JG_ZZ_AUDIT_AP_PKG TR JE İspanya AB Ülkeleri Arasında Fatura Formatı (Documento Equivalente) Imported from BI Publisher Application: Avrupa Yerelleştirmeleri Source: İspanya AB Ülkeleri Arasında Fatura Formatı (Documento Equivalente) Short Name: JEESDOCE_XMLP DB package: JG_ZZ_AUDIT_AP_PKG US JE Spanish Inter-EU Invoice Format (Documento Equivalente) Imported from BI Publisher Application: European Localizations Source: Spanish Inter-EU Invoice Format (Documento Equivalente) Short Name: JEESDOCE_XMLP DB package: JG_ZZ_AUDIT_AP_PKG BI Publisher :p_bal_segment :p_debug_flag :p_first_sequence :p_period_name :p_period_name_to :p_period_year :p_report_name :p_tax_type :p_vat_rep_entity_id 1 -20 :p_chart_of_account_id LOV Oracle JG_ZZ_VAT_CHART_OF_ACCT_ID Y select chart_of_accounts_id id, chart_of_accounts_id value, null description from gl_ledgers where ledger_id in (select ledger_id from jg_zz_vat_rep_entities where vat_reporting_entity_id=:$flex$.jgzz_vat_reporting_entity_id and entity_level_code in ('LEDGER','BSV') union select ledger_id from gl_ledger_le_v where legal_entity_id=(select legal_entity_id from jg_zz_vat_rep_entities where vat_reporting_entity_id=:$flex$.jgzz_vat_reporting_entity_id and entity_level_code='LE') and ledger_category_code='PRIMARY') order by value,description select chart_of_accounts_id from gl_ledgers where ledger_id in(select ledger_id from jg_zz_vat_rep_entities where vat_reporting_entity_id= :$flex$.reporting_identifier and entity_level_code in ('LEDGER','BSV') Union select ledger_id from gl_ledger_le_v where legal_entity_id = (select legal_entity_id from jg_zz_vat_rep_entities where vat_reporting_entity_id =:$flex$.reporting_identifier and entity_level_code='LE') and ledger_category_code='PRIMARY' ) Y AR P_CHART_OF_ACCOUNT_ID معرف دليل الحسابات D P_CHART_OF_ACCOUNT_ID Kontenplankennung E P_CHART_OF_ACCOUNT_ID Id Plan de Cuentas F P_CHART_OF_ACCOUNT_ID ID plan de comptes I P_CHART_OF_ACCOUNT_ID Id piano dei conti RU P_CHART_OF_ACCOUNT_ID Идент. плана счетов S P_CHART_OF_ACCOUNT_ID Kontoplans-id US P_CHART_OF_ACCOUNT_ID Chart of Account Id 2 -10 :p_report_name Char JEESDOCE 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 3 10 :p_vat_rep_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 4 20 :p_period_name LOV Oracle JGZZ_TAX_CALENDAR_PERIOD_ENTITY Y select rps.tax_calendar_period id, rps.tax_calendar_period value, null description from jg_zz_vat_rep_status rps where rps.vat_reporting_entity_id=:$flex$.jgzz_vat_reporting_entity_id and rps.rowid in ( select max(rps1.rowid) from jg_zz_vat_rep_status rps1 where rps1.vat_reporting_entity_id=:$flex$.jgzz_vat_reporting_entity_id group by rps1.tax_calendar_period) order by period_start_date Y AR فترة تقويم الضريبة من فترة تقويم الضريبة من D Von Steuerkalenderperiode Von Steuerkalenderperiode E De Período de Calendario Fiscal De Período de Calendario Fiscal F Période du calendrier fiscal - De Période du calendrier fiscal - De I Periodo calendario fiscale -Da Periodo calendario fiscale -Da RU Налоговый календарный период с Налоговый календарный период с S Från skattekalenderperiod Från skattekalenderperiod TR Vergi Takvimi Başlangıç Dönemi Vergi Takvimi Başlangıç Dönemi US Tax Calendar Period From Tax Calendar Period From 5 30 :p_period_name_to LOV Oracle JGZZ_TAX_CALENDAR_PERIOD_TO Y select rps.tax_calendar_period id, rps.tax_calendar_period value, null description from jg_zz_vat_rep_status rps where rps.vat_reporting_entity_id=:$flex$.jgzz_vat_reporting_entity_id and rps.rowid in ( select max(rps1.rowid) from jg_zz_vat_rep_status rps1 where rps1.vat_reporting_entity_id=:$flex$.jgzz_vat_reporting_entity_id and trunc(rps1.period_start_date)>=(select trunc(min(period_start_date)) from jg_zz_vat_rep_status rps2 where rps2.vat_reporting_entity_id=:$flex$.jgzz_vat_reporting_entity_id and rps2.tax_calendar_period=:$flex$.jgzz_tax_calendar_period_entity) group by rps1.tax_calendar_period) order by period_start_date select :$flex$.tax_calendar_period_from from dual Y AR فترة تقويم الضريبة إلى فترة تقويم الضريبة إلى D Bis Steuerkalenderperiode Bis Steuerkalenderperiode E A Período de Calendario Fiscal A Período de Calendario Fiscal F Période du calendrier fiscal - A Période du calendrier fiscal - A I Periodo calendario fiscale - A Periodo calendario fiscale - A RU Налоговый календарный период по Налоговый календарный период по S Till skattekalenderperiod Till skattekalenderperiod TR Vergi Takvimi Bitiş Dönemi Vergi Takvimi Bitiş Dönemi US Tax Calendar Period To Tax Calendar Period To 6 40 :p_tax_type LOV Oracle JGZZ_TAX_TYPE Y select zrc.reporting_code_char_value id, zrc.reporting_code_char_value value, zrctl.reporting_code_name description from zx_reporting_types_b zrt,zx_reporting_codes_b zrc,zx_reporting_codes_tl zrctl,jg_zz_vat_rep_entities rps where zrt.reporting_type_code='EMEA_VAT_REPORTING_TYPE' and zrt.reporting_type_id=zrc.reporting_type_id and zrc.reporting_code_id=zrctl.reporting_code_id and sysdate between nvl(zrc.effective_from,sysdate) and nvl(zrc.effective_to,sysdate) and zrctl.language in userenv('LANG') and rps.vat_reporting_entity_id=(select nvl(mapping_vat_rep_entity_id,vat_reporting_entity_id) from jg_zz_vat_rep_entities where vat_reporting_entity_id=:$flex$.jgzz_vat_reporting_entity_id) and nvl(zrt.tax_regime_code,rps.tax_regime_code)=rps.tax_regime_code order by value,description AR رمز نوع تقرير الضريبة نوع الضريبة D Schlüssel Steuerauswertungsart Steuerart E Código de Tipo de Declaración de Impuestos Tipo de Impuesto F Code type de déclaration fiscale Type de taxe I Codice tipo di rapporti imposte Tipo di imposta RU Код типа налоговой отчетности Тип налога S Kod för skatterapporteringstyp Skattetyp TR Vergi Raporlama Tipi Kodu Vergi Tipi US Tax Reporting Type Code Tax Type 7 50 :p_first_sequence Number AR الرقم المسلسل الأول الرقم المسلسل الأول D Erste Belegnummer Erste Belegnummer E Primer Número de Secuencia Primer Número de Secuencia F Premier numéro d'ordre Premier numéro d'ordre I Primo numero sequenza Primo numero sequenza RU Первый порядковый номер Первый порядковый номер S Första sekvensnumret Första sekvensnumret TR İlk Sıra Numarası İlk Sıra Numarası US First Sequence Number First Sequence Number 8 60 :p_bal_segment Char AR مقطع الترصيد مقطع الترصيد D Buchungskreis Buchungskreis E Segmento de Balance Segmento de Balance F Segment d'équilibrage Segment d'équilibrage I Segmento di quadratura Segmento di quadratura RU Балансирующий сегмент Балансирующий сегмент S Balanssegment Balanssegment TR Dengeleme Segmenti Dengeleme Segmenti US Balancing Segment Balancing Segment