ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4708651A9E05362FB09050512 SELECT CURR_INV_FLAG , RACL_INV_CUSTOMER_ID , INVOICE_COUNT , PRIOR_INVOICE_COUNT , TAXABLE_AMOUNT , TAX_AMOUNT , EXEMPT_AMOUNT , NONTAXABLE_AMOUNT , CF_TOTAL_AMOUNT FROM (SELECT 'Y' CURR_INV_FLAG ,nvl( invlines.jg_info_n3 /*sold_to_customer_id*/ , invlines.jg_info_n4 /*bill_to_customer_id*/ ) racl_inv_customer_id /* curr_inv_customer_id */ ,count(distinct invlines.jg_info_n1 /*customer_trx_id*/) INVOICE_COUNT , 0 PRIOR_INVOICE_COUNT ,sum(decode(l1.lookup_code || l2.lookup_code, '' ,decode(invlines.jg_info_v3 /*line_type*/, 'LINE' ,round(invlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0) ,0) ,0) ) TAXABLE_AMOUNT ,sum(decode(l1.lookup_code || l2.lookup_code, '' ,decode(taxlines.jg_info_v3 /*line_type*/, 'TAX' ,round(taxlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0) ,0) ,0) ) TAX_AMOUNT ,sum(decode(l1.lookup_code, '' ,0 ,decode(invlines.jg_info_v3 /*line_type*/, 'LINE' ,round(invlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0) ,0 )) ) EXEMPT_AMOUNT ,sum(decode(l2.lookup_code, '' ,0 ,decode(invlines.jg_info_v3 /*line_type*/ , 'LINE' ,round(invlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0) ,0 ) ) ) NONTAXABLE_AMOUNT, jg_zz_rtce_dt_pkg.cf_total_amount(sum ( decode ( l1.lookup_code || l2.lookup_code , '' , decode ( invlines.jg_info_v3 , 'LINE' , round ( invlines.jg_info_n10 * nvl ( invlines.jg_info_n5 , 1 ) , 0 ) , 0 ) , 0 ) ), sum ( decode ( l1.lookup_code || l2.lookup_code , '' , decode ( taxlines.jg_info_v3 , 'TAX' , round ( taxlines.jg_info_n10 * nvl ( invlines.jg_info_n5 , 1 ) , 0 ) , 0 ) , 0 ) ), sum ( decode ( l1.lookup_code , '' , 0 , decode ( invlines.jg_info_v3 , 'LINE' , round ( invlines.jg_info_n10 * nvl ( invlines.jg_info_n5 , 1 ) , 0 ) , 0 ) ) ), sum ( decode ( l2.lookup_code , '' , 0 , decode ( invlines.jg_info_v3 , 'LINE' , round ( invlines.jg_info_n10 * nvl ( invlines.jg_info_n5 , 1 ) , 0 ) , 0 ) ) )) CF_TOTAL_AMOUNT from jg_zz_vat_trx_gt invlines ,jg_zz_vat_trx_gt taxlines ,zx_rates_b zxrates ,zx_taxes_b zxtaxes ,zx_report_codes_assoc zxass ,ar_lookups l1 ,ar_lookups l2 ,ar_lookups l3 where :P_CALLED_FROM = 'JEITRACL' and nvl(invlines.jg_info_n3, invlines.jg_info_n4) = :customer_account_id and invlines.jg_info_v3 /*line_type*/ = 'LINE' and invlines.jg_info_n7 /*customer_trx_line_id*/ = taxlines.jg_info_n9 /*link_to_cust_trx_line_id*/ and taxlines.jg_info_v3 /*line_type*/ = 'TAX' and taxlines.jg_info_n11 /*vat_tax_id*/ = zxrates.tax_rate_id(+) and zxrates.content_owner_id = zxtaxes.content_owner_id and zxrates.tax_regime_code = zxtaxes.tax_regime_code and zxrates.tax = zxtaxes.tax and zxrates.tax_rate_id = zxass.entity_id and zxass.entity_code = 'ZX_RATES' and l1.lookup_type(+) = 'JE_EXEMPT_TAX_TYPE' and zxass.REPORTING_CODE_CHAR_VALUE = l1.lookup_code(+) and l2.lookup_type(+) = 'JE_NONTAXABLE_TAX_TYPE' and zxass.REPORTING_CODE_CHAR_VALUE = l2.lookup_code(+) and l3.lookup_type(+) = 'JE_DEFERRED_TAX_TYPE' and zxrates.def_rec_settlement_option_code = l3.lookup_code(+) and ( ( invlines.JG_INFO_D2 /* printing_original_date*/ between jg_zz_rtce_dt_pkg.cf_period_start_date and jg_zz_rtce_dt_pkg.cf_period_end_date and not exists ( select 1 from ra_cust_trx_line_gl_dist rctlgd where rctlgd.customer_trx_line_id = invlines.jg_info_n7 /*customer_trx_line_id*/ /* and rctlgd.gl_posted_date is null */ and rctlgd.gl_date between jg_zz_rtce_dt_pkg.cf_period_start_date and jg_zz_rtce_dt_pkg.cf_period_end_date ) and l3.lookup_type is null ) or ( exists ( select ara.gl_date from ar_receivable_applications ara ,ar_cash_receipts acr where ara.applied_customer_trx_id = invlines.jg_info_n1/*customer_trx_id*/ and ara.status = 'APP' and ara.cash_receipt_id = acr.cash_receipt_id group by ara.gl_date having min(ara.gl_date) between jg_zz_rtce_dt_pkg.cf_period_start_date and jg_zz_rtce_dt_pkg.cf_period_end_date ) and l3.lookup_type is not null ) or ( exists ( select null from ra_cust_trx_line_gl_dist crd ,ra_customer_trx_lines crl ,ra_customer_trx crh where crh.customer_trx_id = invlines.jg_info_n6 /*previous_customer_trx_id*/ and crh.customer_trx_id = crl.customer_trx_id and crl.customer_trx_line_id = crd.customer_trx_line_id /* and crd.gl_posted_date is not null */ and crd.gl_date between jg_zz_rtce_dt_pkg.cf_period_start_date and jg_zz_rtce_dt_pkg.cf_period_end_date ) and l3.lookup_type is not null ) ) group by nvl(invlines.jg_info_n3 /*sold_to_customer_id*/, invlines.jg_info_n4 /*bill_to_customer_id*/) UNION SELECT 'Y' CURR_INV_FLAG ,null RACL_INV_CUSTOMER_ID ,0 INVOICE_COUNT ,0 PRIOR_INVOICE_COUNT ,0 TAXABLE_AMOUNT ,0 TAX_AMOUNT ,0 EXEMPT_AMOUNT ,0 NONTAXABLE_AMOUNT ,0 CF_TOTAL_AMOUNT FROM DUAL UNION /* Following query will return the prior year invoices accounted this year */ select 'N' CURR_INV_FLAG ,nvl( invlines.jg_info_n3 /*sold_to_customer_id*/ , invlines.jg_info_n4 /*bill_to_customer_id*/ ) racl_inv_customer_id /* prior_inv_customer_id */ , 0 INVOICE_COUNT ,count(distinct invlines.jg_info_n1 /*customer_trx_id*/) PRIOR_INVOICE_COUNT ,sum(decode(l1.lookup_code || l2.lookup_code, '' ,decode(invlines.jg_info_v3 /*line_type*/, 'LINE' ,round(invlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0) ,0 ) ,0) ) TAXABLE_AMOUNT ,sum(decode(l1.lookup_code || l2.lookup_code, '' ,decode(taxlines.jg_info_v3 /*line_type*/, 'TAX' ,round(taxlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0) ,0 ) ,0) ) TAX_AMOUNT ,sum(decode(l1.lookup_code, '' ,0 ,decode(invlines.jg_info_v3 /*line_type*/, 'LINE' ,round(invlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0) ,0 ) ) ) EXEMPT_AMOUNT ,sum(decode(l2.lookup_code, '' ,0 ,decode(invlines.jg_info_v3 /*line_type*/ , 'LINE' ,round(invlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0) ,0 ) ) ) NONTAXABLE_AMOUNT ,jg_zz_rtce_dt_pkg.cf_total_amount ( sum(decode(l1.lookup_code || l2.lookup_code, '' ,decode(invlines.jg_info_v3 /*line_type*/, 'LINE' ,round(invlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0) ,0 ) ,0) ) /*taxable_amount*/ , sum(decode(l1.lookup_code || l2.lookup_code, '' ,decode(taxlines.jg_info_v3 /*line_type*/, 'TAX' ,round(taxlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0) ,0 ) ,0) ) /*tax_amount*/ , sum(decode(l1.lookup_code, '' ,0 ,decode(invlines.jg_info_v3 /*line_type*/, 'LINE' ,round(invlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0) ,0 ) ) ) /*exempt_amount*/ , sum(decode(l2.lookup_code, '' ,0 ,decode(invlines.jg_info_v3 /*line_type*/ , 'LINE' ,round(invlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0) ,0 ) ) ) /*nontaxable_amount*/ ) CF_TOTAL_AMOUNT from jg_zz_vat_trx_gt invlines ,jg_zz_vat_trx_gt taxlines ,zx_rates_b zxrates ,zx_taxes_b zxtaxes ,zx_report_codes_assoc zxass ,ar_lookups l1 ,ar_lookups l2 ,ar_lookups l3 where :P_CALLED_FROM = 'JEITRACL' and nvl( invlines.jg_info_n3,invlines.jg_info_n4) = :customer_account_id and invlines.jg_info_v3 /*line_type*/ = 'LINE' and invlines.jg_info_n7 /*customer_trx_line_id*/ = taxlines.jg_info_n9 /*link_to_cust_trx_line_id*/ and taxlines.jg_info_v3 /*line_type*/ = 'TAX' and taxlines.jg_info_n11 /*vat_tax_id*/ = zxrates.tax_rate_id(+) and zxrates.content_owner_id = zxtaxes.content_owner_id and zxrates.tax_regime_code = zxtaxes.tax_regime_code and zxrates.tax = zxtaxes.tax and zxrates.tax_rate_id = zxass.entity_id and zxass.entity_code = 'ZX_RATES' and l1.lookup_type(+) = 'JE_EXEMPT_TAX_TYPE' and zxass.REPORTING_CODE_CHAR_VALUE = l1.lookup_code(+) and l2.lookup_type(+) = 'JE_NONTAXABLE_TAX_TYPE' and zxass.REPORTING_CODE_CHAR_VALUE = l2.lookup_code(+) and l3.lookup_type(+) = 'JE_DEFERRED_TAX_TYPE' and zxrates.def_rec_settlement_option_code = l3.lookup_code(+) and invlines.JG_INFO_D2 /* printing_original_date */ between add_months( jg_zz_rtce_dt_pkg.cf_period_start_date,-12) and add_months(jg_zz_rtce_dt_pkg.cf_period_end_date, -12) and exists ( select 1 from ra_cust_trx_line_gl_dist rctlgd where rctlgd.customer_trx_line_id = invlines.jg_info_n7 /*customer_trx_line_id*/ /* and rctlgd.gl_posted_date is not null */ and rctlgd.gl_date between jg_zz_rtce_dt_pkg.cf_period_start_date and jg_zz_rtce_dt_pkg.cf_period_end_date ) and ( exists ( select 'x' from ar_receivable_applications ara ,ar_cash_receipts acr where ara.applied_customer_trx_id = invlines.jg_info_n1/*customer_trx_id*/ and ara.status = 'APP' and ara.cash_receipt_id = acr.cash_receipt_id ) or l3.lookup_type is null ) and ( exists ( select null from ra_cust_trx_line_gl_dist crd ,ra_customer_trx_lines crl ,ra_customer_trx crh where crh.customer_trx_id = invlines.jg_info_n6 /*previous_customer_trx_id*/ and crh.customer_trx_id = crl.customer_trx_id and crl.customer_trx_line_id = crd.customer_trx_line_id /* and crd.gl_posted_date is not null */ and crd.gl_date between jg_zz_rtce_dt_pkg.cf_period_start_date and jg_zz_rtce_dt_pkg.cf_period_end_date ) or l3.lookup_type is not null ) group by nvl( invlines.jg_info_n3 /*sold_to_customer_id*/ , invlines.jg_info_n4 /*bill_to_customer_id*/ ) UNION SELECT 'N' CURR_INV_FLAG ,null RACL_INV_CUSTOMER_ID ,0 INVOICE_COUNT ,0 PRIOR_INVOICE_COUNT ,0 TAXABLE_AMOUNT ,0 TAX_AMOUNT ,0 EXEMPT_AMOUNT ,0 NONTAXABLE_AMOUNT ,0 CF_TOTAL_AMOUNT FROM DUAL) ORDER BY DECODE(CURR_INV_FLAG, 'Y', 1, 'N', 2) Y JE JEPTARVR_XMLP AR JE تقرير إجمالي عدد العملاء البرتغاليين Imported from BI Publisher Application: المحليات الأوروبية Source: تقرير إجمالي عدد العملاء البرتغاليين Short Name: JEPTARVR_XMLP DB package: JG_ZZ_RTCE_DT_PKG D JE Kunden - USt-Übersicht Imported from BI Publisher Description: Portugal: Auswertung "Kunden - USt-Übersicht" Application: Europäische Lokalisierungen Source: Portugal: Kunden - USt-Übersicht Short Name: JEPTARVR_XMLP DB package: JG_ZZ_RTCE_DT_PKG E JE Portugal - Informe Recapitulativo de Clientes Imported from BI Publisher Application: Localizaciones Europeas Source: Portugal - Informe Recapitulativo de Clientes Short Name: JEPTARVR_XMLP DB package: JG_ZZ_RTCE_DT_PKG F JE Portugal - Etat récapitulatif des clients Imported from BI Publisher Application: European Localizations Source: Portugal - Etat récapitulatif des clients Short Name: JEPTARVR_XMLP DB package: JG_ZZ_RTCE_DT_PKG I JE Portogallo - rapporto Riepilogo clienti Imported from BI Publisher Application: European Localizations Source: Portogallo - rapporto Riepilogo clienti Short Name: JEPTARVR_XMLP DB package: JG_ZZ_RTCE_DT_PKG RU JE Краткий отчет о заказчиках (Португалия) Imported from BI Publisher Application: Версии для Европы Source: Краткий отчет о заказчиках (Португалия) Short Name: JEPTARVR_XMLP DB package: JG_ZZ_RTCE_DT_PKG S JE Sammanfattande rapport över kunder Imported from BI Publisher Application: European Localizations Source: Portugal: Sammanfattande rapport över kunder Short Name: JEPTARVR_XMLP DB package: JG_ZZ_RTCE_DT_PKG TR JE Portuguese Customers Recapitulative Imported from BI Publisher Application: Avrupa Yerelleştirmeleri Source: Portuguese Customers Recapitulative Report Short Name: JEPTARVR_XMLP DB package: JG_ZZ_RTCE_DT_PKG US JE Portuguese Customers Recapitulative Imported from BI Publisher Application: European Localizations Source: Portuguese Customers Recapitulative Report Short Name: JEPTARVR_XMLP DB package: JG_ZZ_RTCE_DT_PKG BI Publisher :customer_account_id :gd_period_end_date :gd_period_start_date :gn_legal_entity_id :gn_tot_customers :gv_balancing_segment_value :gv_chart_of_accounts_id :gv_country :gv_currency_code :gv_ledger_id :gv_prev_fiscal_code :gv_repent_address_line_1 :gv_repent_address_line_2 :gv_repent_address_line_3 :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 :p_called_from :p_dec_type :p_fiscal_year :p_min_inv_amt :p_reporting_entity_id 1 -10 :p_called_from Char JEPTARVR 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