ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4706F51A9E05362FB09050512Y SELECT glp.period_num --,count(distinct abc.vendor_id4) vendor_count ,count(distinct abc.taxpayer_id1) vendor_count ,NVL(sum(abc.gamount),0) gamount ,decode(sign(nvl(sum(abc.gawt),0)), -1, '-', '0') || substr(to_char(round(sum(abc.gawt)), '00000000000D00'), 2, 11) gawt ,decode(sign(nvl(sum(abc.gawt),0)- nvl(sum(abc.dawt),0)), -1, '-', '0') || nvl(substr(to_char(round(sum(abc.gawt) - sum(abc.dawt)), '00000000000D00'), 2, 11),'000000000000') gdawt ,NVL(sum(abc.damount),0) damount ,NVL(sum(abc.dawt),0) dawt ,round(NVL(sum(abc.gamount),0) - NVL(sum(abc.damount),0)) gamount3 FROM (SELECT e.vendor_site_id3 vendor_site_id4 ,e.vendor_id3 vendor_id4 ,sum(e.gross_amount) gamount ,sum(e.awt_amount) gawt ,sum(e.div_amount) damount ,sum(e.div_awt) dawt ,e.period ,e.taxpayer_id1 taxpayer_id1 FROM ( SELECT d.vendor_site_id3 vendor_site_id3 ,d.vendor_id3 vendor_id3 ,(CASE WHEN d.gamount_filter > 0 THEN d.gross_amount ELSE 0 END) gross_amount ,(CASE WHEN d.gawt_filter > 0 THEN d.awt_amount ELSE 0 END) awt_amount ,d.div_amount div_amount ,d.div_awt div_awt ,d.period ,d.taxpayer_id1 taxpayer_id1 FROM (SELECT c.vendor_site_id3 vendor_site_id3 ,c.vendor_id3 vendor_id3 ,c.gross_amount gross_amount ,c.awt_amount awt_amount ,Sum(c.gross_amount) over (Partition By c.Vendor_Site_ID3) gamount_filter ,Sum(c.awt_amount) over (Partition By c.Vendor_Site_ID3) gawt_filter ,c.div_amount div_amount ,c.div_awt div_awt ,c.period ,c.taxpayer_id1 taxpayer_id1 FROM ( Select a.* From ( SELECT distinct ac.check_id Check_ID, ac.vendor_id vendor_id3, ac.vendor_site_id Vendor_Site_ID3, &p_tax_payerid_cond taxpayer_id1, ac.check_number Check_Number, ac.check_date Check_Date, JE_IL_TAX_PKG.get_gross_amount(ai.invoice_id,aip.check_id,:P_START_DATE,:P_END_DATE,aip.reversal_inv_pmt_id) gross_amount, JE_IL_TAX_PKG.get_awt_amt(Ai.Invoice_Id,Aip.Check_Id,:P_START_DATE,:P_END_DATE,aip.reversal_inv_pmt_id) Awt_Amount, JE_IL_TAX_PKG.get_div_amt(Ai.Invoice_Id,Aip.Check_Id,:P_START_DATE,:P_END_DATE,aip.reversal_inv_pmt_id) Div_Amount, JE_IL_TAX_PKG.get_div_awt(Ai.Invoice_Id,Aip.Check_Id,:P_START_DATE,:P_END_DATE,aip.reversal_inv_pmt_id) Div_Awt, ibyp.payment_method_name Payment_Type, to_char(aip.accounting_date,'MM') period, row_number( ) over (partition by aip.invoice_id,aip.check_id,aip.accounting_event_id order by aip.invoice_id,aip.check_id,aip.accounting_event_id) SLNO FROM ap_invoices_all ai, ap_checks_all ac, ap_invoice_payments_all aip, iby_payment_methods_vl ibyp, po_vendors pvend, po_vendor_sites_all pvs, (SELECT distinct person_id ,national_identifier FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf WHERE ai.vendor_site_id = ac.vendor_site_id AND ai.vendor_id = pvend.vendor_id AND ai.vendor_site_id = pvs.vendor_site_id AND pvend.vendor_id = pvs.vendor_id And Nvl(Pvend.Employee_Id, -99) = Papf.Person_Id (+) AND ac.payment_method_code = ibyp.payment_method_code AND ai.invoice_id = aip.invoice_id AND ac.check_id = aip.check_id AND aip.reversal_inv_pmt_id IS NULL AND ai.legal_entity_id = :P_Legal_Entity_ID -- AND JE_IL_TAX_PKG.get_gross_amount(ai.invoice_id,aip.check_id,:P_START_DATE ,:P_END_DATE,aip.reversal_inv_pmt_id) <> 0 --Bug 12612501 AND (JE_IL_TAX_PKG.get_gross_amount(ai.invoice_id,aip.check_id,:P_START_DATE ,:P_END_DATE,aip.reversal_inv_pmt_id) <> 0 OR JE_IL_TAX_PKG.get_awt_amount <> 0 ) -- Bug 12612501 AND EXISTS ( select 1 from ap_invoice_distributions_all aid WHERE aid.invoice_id = ai.invoice_id AND aid.line_type_lookup_code = 'AWT' AND aid.awt_flag ='A' AND aid.awt_invoice_payment_id IS NOT NULL AND aid.awt_invoice_payment_id = aip.invoice_payment_id AND aid.accounting_date >= :P_START_DATE AND aid.accounting_date <= :P_END_DATE UNION SELECT 1 FROM AP_INVOICES_ALL ai1 ,AP_INVOICE_PAYMENTS_ALL aip1 ,AP_CHECKS_ALL ac1 ,po_vendors pvend1 ,po_vendor_sites_all pvs1 WHERE ai1.invoice_id = ai.invoice_id AND ai1.invoice_id= aip1.invoice_id AND ai1.vendor_id = pvend1.vendor_id AND ai1.vendor_site_id = pvs1.vendor_site_id AND pvend1.vendor_id = pvs1.vendor_id AND aip1.check_id = ac1.check_id AND ai1.legal_entity_id = :P_Legal_Entity_ID AND ai1.set_of_books_id = &l_primary_ledger_id AND aip1.set_of_books_id= &l_primary_ledger_id AND ac1.global_attribute_category='JE.IL.APXPAWKB.CHECKS' AND NVL(ac1.global_attribute1,0) > 0 AND aip1.accounting_date >=:P_START_DATE AND aip1.accounting_date <=:P_END_DATE &l_currency_check &l_foreign_suppliers_check) &p_vendor_type_cond ) a where a.slno=1 UNION ALL Select b.* From ( SELECT distinct ac.check_id Check_ID, ac.vendor_id vendor_id3, ac.vendor_site_id Vendor_Site_ID3, &p_tax_payerid_cond taxpayer_id1, ac.check_number Check_Number, ac.void_date Check_Date, JE_IL_TAX_PKG.get_gross_amount(ai.invoice_id,aip.check_id,:P_START_DATE,:P_END_DATE,aip.reversal_inv_pmt_id) gross_amount, JE_IL_TAX_PKG.get_awt_amt(Ai.Invoice_Id,Aip.Check_Id,:P_START_DATE,:P_END_DATE,aip.reversal_inv_pmt_id) Awt_Amount, JE_IL_TAX_PKG.get_div_amt(Ai.Invoice_Id,Aip.Check_Id,:P_START_DATE,:P_END_DATE,aip.reversal_inv_pmt_id) Div_Amount, JE_IL_TAX_PKG.get_div_awt(Ai.Invoice_Id,Aip.Check_Id,:P_START_DATE,:P_END_DATE,aip.reversal_inv_pmt_id) Div_Awt, ibyp.payment_method_name Payment_Type, to_char(aip.accounting_date,'MM') period, row_number( ) over (partition by aip.invoice_id,aip.check_id,aip.accounting_event_id order by aip.invoice_id,aip.check_id,aip.accounting_event_id) SLNO FROM ap_invoices_all ai, ap_checks_all ac, ap_invoice_payments_all aip, iby_payment_methods_vl ibyp, po_vendors pvend, po_vendor_sites_all pvs, (SELECT distinct person_id ,national_identifier FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf WHERE ai.vendor_site_id = ac.vendor_site_id AND ai.vendor_id = pvend.vendor_id AND ai.vendor_site_id = pvs.vendor_site_id AND pvend.vendor_id = pvs.vendor_id And Nvl(Pvend.Employee_Id, -99) = Papf.Person_Id (+) AND ac.payment_method_code = ibyp.payment_method_code AND ai.invoice_id = aip.invoice_id AND ac.check_id = aip.check_id AND aip.reversal_inv_pmt_id IS NOT NULL AND ai.legal_entity_id = :P_Legal_Entity_ID -- AND JE_IL_TAX_PKG.get_gross_amount(ai.invoice_id,aip.check_id,:P_START_DATE ,:P_END_DATE,aip.reversal_inv_pmt_id) <> 0 --Bug 12612501 AND (JE_IL_TAX_PKG.get_gross_amount(ai.invoice_id,aip.check_id,:P_START_DATE ,:P_END_DATE,aip.reversal_inv_pmt_id) <> 0 OR JE_IL_TAX_PKG.get_awt_amount <> 0 ) -- Bug 12612501 AND EXISTS ( select 1 from ap_invoice_distributions_all aid WHERE aid.invoice_id = ai.invoice_id AND aid.line_type_lookup_code = 'AWT' AND aid.awt_flag ='A' AND aid.awt_invoice_payment_id IS NOT NULL AND aid.awt_invoice_payment_id = aip.invoice_payment_id AND aid.accounting_date >= :P_START_DATE AND aid.accounting_date <= :P_END_DATE UNION SELECT 1 FROM AP_INVOICES_ALL ai1 ,AP_INVOICE_PAYMENTS_ALL aip1 ,AP_CHECKS_ALL ac1 ,po_vendors pvend1 ,po_vendor_sites_all pvs1 WHERE ai1.invoice_id = ai.invoice_id AND ai1.invoice_id= aip1.invoice_id AND ai1.vendor_id = pvend1.vendor_id AND ai1.vendor_site_id = pvs1.vendor_site_id AND pvend1.vendor_id = pvs1.vendor_id AND aip1.check_id = ac1.check_id AND ai1.legal_entity_id = :P_Legal_Entity_ID AND ai1.set_of_books_id = &l_primary_ledger_id AND aip1.set_of_books_id= &l_primary_ledger_id AND ac1.global_attribute_category='JE.IL.APXPAWKB.CHECKS' AND NVL(ac1.global_attribute1,0) > 0 AND aip1.accounting_date >=:P_START_DATE AND aip1.accounting_date <=:P_END_DATE &l_currency_check &l_foreign_suppliers_check) &p_vendor_type_cond ) b where b.slno=1 ) c ) d Where (d.gawt_filter > 0 OR d.gamount_filter > 0) ) e group by e.period,e.Vendor_ID3,e.taxpayer_id1,e.Vendor_Site_ID3 having sum(e.gross_amount) <> 0 or sum(e.awt_amount)<> 0 ) abc ,(select rownum period_num from dual connect by rownum < 13) glp WHERE glp.period_num = to_number(abc.period(+)) -- and :P_REPORT_NAME = 'JEILWHTT' group by glp.period_num order by glp.period_num JE JEILWHTR AR JE تقرير تسوية الضريبة المستقطعة في إسرائيل Imported from BI Publisher Application: المحليات الأوروبية Source: تقرير تسوية الضريبة المستقطعة في إسرائيل Short Name: JEILWHTR DB package: JE_IL_TAX_PKG D JE Steuereinbehaltung - Abstimmung Imported from BI Publisher Description: Israel: Auswertung "Steuereinbehaltung - Abstimmung" Application: Europäische Lokalisierungen Source: Israel: Steuereinbehaltung - Abstimmung Short Name: JEILWHTR DB package: JE_IL_TAX_PKG E JE Israel - Informe de Conciliación de Retención de Impuestos Imported from BI Publisher Application: Localizaciones Europeas Source: Israel - Informe de Conciliación de Retención de Impuestos Short Name: JEILWHTR DB package: JE_IL_TAX_PKG F JE Israël - Etat de rapprochement de retenue fiscale Imported from BI Publisher Application: European Localizations Source: Israël - Etat de rapprochement de retenue fiscale Short Name: JEILWHTR DB package: JE_IL_TAX_PKG I JE Israele - rapporto Quadratura ritenute fiscali Imported from BI Publisher Application: European Localizations Source: Israele - rapporto Quadratura ritenute fiscali Short Name: JEILWHTR DB package: JE_IL_TAX_PKG RU JE Отчет о выверке удержания налога (Израиль) Imported from BI Publisher Application: Версии для Европы Source: Отчет о выверке удержания налога (Израиль) Short Name: JEILWHTR DB package: JE_IL_TAX_PKG S JE Rapport över avstämning av preliminärskatt Imported from BI Publisher Application: European Localizations Source: Israel: Rapport över avstämning av preliminärskatt Short Name: JEILWHTR DB package: JE_IL_TAX_PKG TR JE İsrail Stopaj Vergisi Mutabakatı Raporu Imported from BI Publisher Application: Avrupa Yerelleştirmeleri Source: İsrail Stopaj Vergisi Mutabakatı Raporu Short Name: JEILWHTR DB package: JE_IL_TAX_PKG US JE Israeli Withholding Tax Reconciliation Imported from BI Publisher Application: European Localizations Source: Israeli Withholding Tax Reconciliation Report Short Name: JEILWHTR DB package: JE_IL_TAX_PKG BI Publisher &l_currency_check &l_foreign_suppliers_check &l_primary_ledger_id &p_tax_payerid_cond &p_vendor_type_cond :fnd_debug_log :gn_awt_amount :gn_check_id :gn_div_awt :gn_invoice_id :l_currency_check :l_foreign_suppliers_check :l_foreign_suppliers_check1 :l_period_set_name :l_primary_ledger_id :p_awt_taxrates_query :p_bank_supplier :p_business_sec_cond :p_comp_rpt_exist :p_count_lines_query :p_count_vendors_query :p_deduction_type_cond :p_definition_code :p_end_date :p_exp_irs_cond :p_first_party_query :p_flex_value_cond :p_from_period :p_from_supplier_number :p_global_cond :p_include_wht_0 :p_information_level :p_ledger_id :p_legal_entity_id :p_manual_rpt_exist :p_method :p_name_level :p_order_by :p_order_by_cond :p_payer_position :p_payment_info_query :p_report_name :p_start_date :p_supplier_num_from :p_supplier_num_to :p_supplier_type :p_tax_payerid_cond :p_to_period :p_to_supplier_number :p_vat_reg_no :p_vendor_balance_query :p_vendor_name :p_vendor_name_col :p_vendor_name_cond :p_vendor_site :p_vendor_site_cond :p_vendor_sitecode_col :p_vendor_type :p_vendor_type_col :p_vendor_type_cond :p_year 1 -20 :p_report_name Char JEILWHTR AR اسم التقرير اسم التقرير D Auswertungsbezeichnung Auswertungsbezeichnung E Nombre del Informe Nombre del Informe F Nom de l'état Nom de l'état I Nome rapporto Nome rapporto RU Имя отчета Имя отчета S Rapport Rapport US Report Name Report Name 2 10 :p_from_period LOV Oracle JEIL_TAX_CAL_PERIOD_FROM Y select period_name id, period_name value, null description from gl_periods where period_set_name=fnd_profile.value('JEIL_TAX_CALENDAR') order by period_year,period_type,period_num Y AR من الفترة من الفترة D Von Periode Von Periode E De Período De Período F Période - De Période - De I Periodo - Da Periodo - Da RU Начальный период Начальный период S Från period Från period TR İlk Dönem İlk Dönem US From Period From Period 3 20 :p_to_period LOV Oracle JEIL_TAX_CAL_PERIOD_TO Y select gpt.period_name id, gpt.period_name value, null description from gl_periods gpf,gl_periods gpt where gpt.period_set_name=fnd_profile.value('JEIL_TAX_CALENDAR') and gpf.period_set_name=gpt.period_set_name and gpf.period_type=gpt.period_type and gpf.period_name=:$flex$.jeil_tax_cal_period_from and (gpt.period_year=gpf.period_year and gpt.period_num>=gpf.period_num) order by gpt.period_year,gpt.period_num SELECT :$flex$.from_period FROM DUAL Y AR إلى الفترة إلى الفترة D Bis Periode Bis Periode E A Período A Período F Période - A Période - A I Periodo - A Periodo - A RU Конечный период Конечный период S Till period Till period TR Son Dönem Son Dönem US To Period To Period 4 30 :p_ledger_id LOV Oracle JGZZ_SRS_LEDGER_NAME Y select l.ledger_id id, l.name value, l.description description from gl_access_set_ledgers ac,gl_ledgers_public_v l where ac.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and l.ledger_id=ac.ledger_id order by name Y AR دفتر الأستاذ دفتر الأستاذ D Buch Buch E Libro Mayor Libro Mayor F Livre Livre I Libro contabile Libro contabile RU Книга Книга S Huvudbok Huvudbok US Ledger Ledger 5 40 :p_legal_entity_id LOV Oracle JGZZ_LEGAL_ENTITY Y select legal_entity_id id, legal_entity_name value, null description from gl_ledger_le_v where ledger_id=:$flex$.jgzz_srs_ledger_name order by value,description Y AR الكيان القانوني الكيان القانوني D Mandant Mandant E Entidad Jurídica Entidad Jurídica F Entité juridique Entité juridique I Persona giuridica Persona giuridica RU Юридическое лицо Юридическое лицо S Juridisk enhet Juridisk enhet TR Tüzel Kişilik Tüzel Kişilik US Legal Entity Legal Entity 6 50 :p_vendor_name LOV Oracle JEIL_VENDOR_NAMES Y select vendor_id id, vendor_name value, null description from po_vendors where nvl(allow_awt_flag,'N')='Y' order by value,description AR اسم المورد اسم المورد D Lieferantenname Lieferantenname E Nombre de Proveedor Nombre de Proveedor F Nom du fournisseur Nom du fournisseur I Nome del fornitore Nome del fornitore RU Имя поставщика Имя поставщика S Leverantör Leverantör TR Tedarikçi Adı Tedarikçi Adı US Vendor Name Vendor Name 7 60 :p_vendor_site LOV Oracle JEIL_VENDOR_SITES Y select pvs.vendor_site_id id, pvs.vendor_site_code value, null description from po_vendors pv,po_vendor_sites_all pvs where nvl(pv.allow_awt_flag,'N')='Y' and pv.vendor_id=pvs.vendor_id and pv.vendor_id=:$flex$.jeil_vendor_names and mo_global.check_access(pvs.org_id)='Y' order by value,description AR موقع المورد موقع المورد D Lieferantenadresse Lieferantenadresse E Sitio del Proveedor Sitio del Proveedor F Site fournisseur Site fournisseur I Sede fornitore Sede fornitore RU Отделение поставщика Отделение поставщика S Leverantörsplats Leverantörsplats TR Tedarikçi İşletme Birimi Tedarikçi İşletme Birimi US Vendor Site Vendor Site 8 70 :p_vendor_type LOV Oracle JEIL_VENDOR_TYPES Y select lookup_code id, meaning value, description description from fnd_lookups where lookup_type='JEIL_VENDOR_TYPE' order by value,description SELECT meaning FROM fnd_lookups WHERE lookup_type = 'JEIL_VENDOR_TYPE' AND lookup_code = 'A' Y AR نوع المورد نوع المورد D Lieferantenart Lieferantenart E Tipo de Proveedor Tipo de Proveedor F Type de fournisseur Type de fournisseur I Tipo di fornitore Tipo di fornitore RU Тип поставщика Тип поставщика S Leverantörstyp Leverantörstyp US Vendor Type Vendor Type 9 80 :p_order_by LOV Oracle JEIL_ORDER_BY Y select lookup_code id, meaning value, description description from fnd_lookups where lookup_type='JEIL_ORDER_BY' order by value,description SELECT meaning FROM fnd_lookups WHERE lookup_type = 'JEIL_ORDER_BY' AND lookup_code = 'V' Y AR ترتيب حسب ترتيب حسب D Sortieren nach Sortieren nach E Ordenar por Ordenar por F Tri par Tri par I Ordina per Ordina per RU Сортировка Сортировка S Sortera efter Sortera efter TR Sıralama bazı Sıralama bazı US Order by Order by 10 90 :p_information_level LOV Oracle JEIL_LEVEL Y select lookup_code id, meaning value, description description from fnd_lookups where lookup_type='JEIL_LEVEL' order by value,description SELECT meaning FROM fnd_lookups WHERE lookup_type = 'JEIL_LEVEL' AND lookup_code = 'S' Y AR مستوى المعلومات مستوى المعلومات D Informationsebene Informationsebene E Nivel de Información Nivel de Información F Niveau d'information Niveau d'information I Livello informazioni Livello informazioni RU Уровень сведений Уровень сведений S Informationsnivå Informationsnivå US Information Level Information Level 11 100 :p_name_level LOV Oracle JEIL_NAME_LEVEL Y select lookup_code id, meaning value, description description from fnd_lookups where lookup_type='JEIL_NAME_LEVEL' order by value,description SELECT meaning FROM fnd_lookups WHERE lookup_type = 'JEIL_NAME_LEVEL' AND lookup_code = 'VSN' Y AR مستوى الاسم مستوى الاسم D Bezeichnungsebene Bezeichnungsebene E Nivel de Nombre Nivel de Nombre F Niveau de nom Niveau de nom I Livello nome Livello nome RU Уровень имени Уровень имени S Namnnivå Namnnivå US Name Level Name Level