ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4705651A9E05362FB09050512 select tax.tax_rate_code vat_code1, i.invoice_id, NVL(d.merchant_name, v.vendor_name) vendor_name, NVL(d.merchant_reference, v.segment1) vendor_num, i.invoice_num, i.invoice_date, sum(decode(d.line_type_lookup_code, 'REC_TAX', 0, 'NONREC_TAX', 0, decode(i.invoice_currency_code, :FUNC_CURR, d.amount, d.base_amount))) w_orig_costs, sum(decode(d.line_type_lookup_code, 'REC_TAX', decode(i.invoice_currency_code, :FUNC_CURR, d.amount, d.base_amount),0)) w_orig_vat, 0 line_discount, 0 tax_discount, --&invoice_date1 CF_INVOICE_DATE1, JE_JEDEAPVR_XMLP_PKG.CF_INVOICE_DATE1FORMULA(i.invoice_date) CF_INVOICE_DATE1, -- JE_JEDEAPVR_XMLP_PKG.round_orig_costsformula(:inv_orig_cost) round_orig_costs, -- JE_JEDEAPVR_XMLP_PKG.round_orig_vatformula(:inv_vat_cost) round_orig_vat, -- JE_JEDEAPVR_XMLP_PKG.round_disc_costsformula(:inv_disc_cost) round_disc_costs, -- JE_JEDEAPVR_XMLP_PKG.round_disc_vatformula(:inv_vat_disc) round_disc_vat, JE_JEDEAPVR_XMLP_PKG.w_calc_vatformula(:TAX_TYPE, sum ( decode ( d.line_type_lookup_code , 'REC_TAX' , 0 , 'NONREC_TAX' , 0 , decode ( i.invoice_currency_code , :FUNC_CURR , d.amount , d.base_amount ) ) ), :TAX_RATE) w_calc_vat, JE_JEDEAPVR_XMLP_PKG.vat_calc_diffformula(JE_JEDEAPVR_XMLP_PKG.w_calc_vatformula(:TAX_TYPE, sum ( decode ( d.line_type_lookup_code , 'REC_TAX' , 0 , 'NONREC_TAX' , 0 , decode ( i.invoice_currency_code , :FUNC_CURR , d.amount , d.base_amount ) ) ), :TAX_RATE), sum ( decode ( d.line_type_lookup_code , 'REC_TAX' , decode ( i.invoice_currency_code , :FUNC_CURR , d.amount , d.base_amount ) , 0 ) )) vat_calc_diff, JE_JEDEAPVR_XMLP_PKG.get_discount_vatformula(0, i.invoice_id) Get_discount_vat from po_vendors v, ap_invoices_all i, ap_invoice_distributions_all d, gl_period_statuses glp, zx_rates_b tax where i.legal_entity_id = :P_LEGAL_ENTITY_ID and i.set_of_books_id = :P_SET_OF_BOOKS_ID and i.invoice_id = d.invoice_id and v.vendor_id = i.vendor_id and ((glp.period_year*10000) + glp.period_num)||'' between :P_PERIOD_NUM_FROM and :P_PERIOD_NUM_TO and glp.set_of_books_id = :P_SET_OF_BOOKS_ID and glp.application_id = :P_APP_ID and d.period_name = glp.period_name and d.set_of_books_id= :P_SET_OF_BOOKS_ID and d.posted_flag = 'Y' and nvl(d.tax_code_id,JG_ZZ_PTCE_DT_PKG.get_item_tax_code_id(i.invoice_id,d.invoice_distribution_id))=tax.tax_rate_id and tax.tax_rate_code = :name group by tax.tax_rate_code, i.invoice_id, tax.tax_rate_code, NVL(d.merchant_name, v.vendor_name), NVL(d.merchant_reference, v.segment1), i.invoice_num, i.invoice_date, d.period_name union select tax.tax_rate_code vat_code1, i.invoice_id, NVL(d.merchant_name, v.vendor_name) vendor_name, NVL(d.merchant_reference, v.segment1) vendor_num, i.invoice_num, i.invoice_date, 0 w_orig_costs, 0 w_orig_vat, round( sum( decode(d.line_type_lookup_code, 'REC_TAX', 0, 'NONREC_TAX', 0, (nvl(ip.discount_taken , 0) * nvl(ip.exchange_rate,1) ) * (decode(i.invoice_currency_code, :FUNC_CURR, d.amount, nvl(d.base_amount,0)) / decode(decode(i.invoice_currency_code, :FUNC_CURR, i.invoice_amount,nvl(i.base_amount,0)), 0,1, decode(i.invoice_currency_code, :FUNC_CURR, i.invoice_amount,i.base_amount)) ))) ,:CURR_PERC) line_discount, round( sum( decode(d.line_type_lookup_code, 'REC_TAX', ((nvl(ip.discount_taken , 0) * nvl(ip.exchange_rate,1) ) * (decode(i.invoice_currency_code, :FUNC_CURR, d.amount, nvl(d.base_amount,0)) / decode(decode(i.invoice_currency_code, :FUNC_CURR, i.invoice_amount,nvl(i.base_amount,0)), 0,1, decode(i.invoice_currency_code, :FUNC_CURR, i.invoice_amount,i.base_amount)) )), 'NONREC_TAX', ((nvl(ip.discount_taken , 0) * nvl(ip.exchange_rate,1) ) * (decode(i.invoice_currency_code, :FUNC_CURR, d.amount, nvl(d.base_amount,0)) / decode(decode(i.invoice_currency_code, :FUNC_CURR, i.invoice_amount,nvl(i.base_amount,0)), 0,1, decode(i.invoice_currency_code, :FUNC_CURR, i.invoice_amount,i.base_amount)) )), 0)) ,:CURR_PERC) tax_discount, --&invoice_date1 CF_INVOICE_DATE1, JE_JEDEAPVR_XMLP_PKG.CF_INVOICE_DATE1FORMULA(i.invoice_date) CF_INVOICE_DATE1, --JE_JEDEAPVR_XMLP_PKG.round_orig_costsformula(:inv_orig_cost) round_orig_costs, --JE_JEDEAPVR_XMLP_PKG.round_orig_vatformula(:inv_vat_cost) round_orig_vat, --JE_JEDEAPVR_XMLP_PKG.round_disc_costsformula(:inv_disc_cost) round_disc_costs, --JE_JEDEAPVR_XMLP_PKG.round_disc_vatformula(:inv_vat_disc) round_disc_vat, JE_JEDEAPVR_XMLP_PKG.w_calc_vatformula(:TAX_TYPE, 0, :TAX_RATE) w_calc_vat, JE_JEDEAPVR_XMLP_PKG.vat_calc_diffformula(JE_JEDEAPVR_XMLP_PKG.w_calc_vatformula(:TAX_TYPE, 0, :TAX_RATE), 0) vat_calc_diff, JE_JEDEAPVR_XMLP_PKG.get_discount_vatformula(round( sum( decode(d.line_type_lookup_code, 'REC_TAX', ((nvl(ip.discount_taken , 0) * nvl(ip.exchange_rate,1) ) * (decode(i.invoice_currency_code, :FUNC_CURR, d.amount, nvl(d.base_amount,0)) / decode(decode(i.invoice_currency_code, :FUNC_CURR, i.invoice_amount,nvl(i.base_amount,0)), 0,1, decode(i.invoice_currency_code, :FUNC_CURR, i.invoice_amount,i.base_amount)) )), 'NONREC_TAX', ((nvl(ip.discount_taken , 0) * nvl(ip.exchange_rate,1) ) * (decode(i.invoice_currency_code, :FUNC_CURR, d.amount, nvl(d.base_amount,0)) / decode(decode(i.invoice_currency_code, :FUNC_CURR, i.invoice_amount,nvl(i.base_amount,0)), 0,1, decode(i.invoice_currency_code, :FUNC_CURR, i.invoice_amount,i.base_amount)) )), 0)) ,2) , i.invoice_id) Get_discount_vat from po_vendors v, ap_invoices_all i, ap_invoice_payments_all ip, ap_invoice_distributions_all d, gl_period_statuses glp, zx_rates_b tax where i.legal_entity_id = :P_LEGAL_ENTITY_ID and i.set_of_books_id = :P_SET_OF_BOOKS_ID and i.invoice_id = d.invoice_id and ip.invoice_id = i.invoice_id and v.vendor_id = i.vendor_id and ((glp.period_year*10000) + glp.period_num)||'' between :P_PERIOD_NUM_FROM and :P_PERIOD_NUM_TO and glp.set_of_books_id = :P_SET_OF_BOOKS_ID and glp.application_id = :P_APP_ID and ip.period_name = glp.period_name and ip.set_of_books_id = :P_SET_OF_BOOKS_ID and d.posted_flag = 'Y' and nvl(d.tax_code_id,JG_ZZ_PTCE_DT_PKG.get_item_tax_code_id(i.invoice_id,d.invoice_distribution_id))=tax.tax_rate_id and tax.tax_rate_code=:name group by tax.tax_rate_code, i.invoice_id, NVL(d.merchant_name, v.vendor_name), NVL(d.merchant_reference, v.segment1), i.invoice_num, i.invoice_date --order by 4, 6, 5 ORDER BY 4 ASC,3 ASC,1 ASC,5 ASC,6 ASC,2 ASC , 4 , 6 , 5 Y JE JEDEAPVR_XML US JE German Payables VAT Reconciliation Detail Report- Not Supported: Reserved For Future Use Imported from BI Publisher Description: German Payables VAT Reconciliation Detail Report Application: European Localizations Source: German Payables VAT Reconciliation Detail Report (XML) - Not Supported: Reserved For Future Use Short Name: JEDEAPVR_XML DB package: JE_JEDEAPVR_XMLP_PKG BI Publisher &invoice_date1 :curr_perc :dist_opt :flex_data :flex_parm :from_date :func_curr :name :p_app_id :p_bal_seg_name :p_balancing_segment :p_chart_of_acct :p_conc_request_id :p_high_range :p_legal_entity_id :p_low_range :p_period_from :p_period_num_from :p_period_num_to :p_period_to :p_scaling_factor :p_set_of_books_id :p_vat_from :p_vat_to :set_of_books_name :struct_num :tax_rate :tax_type :thru_date 1 -50 :p_app_id Number 200 Y US P_app_id Application Id 2 -40 :p_coa_id LOV Oracle JGZZ_SRS_COA_ID_BYSOB Y select chart_of_accounts_id id, chart_of_accounts_id value, null description from gl_access_sets where access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') order by value,description select chart_of_accounts_id from gl_sets_of_books where set_of_books_id = fnd_profile.value('gl_set_of_bks_id') Y US P_coa_id Chart of Accounts Id 3 -30 :p_chart_of_acct LOV Oracle JG_ZZ_VAT_COA_ID Y select chart_of_accounts_id id, chart_of_accounts_id value, null description from gl_sets_of_books where set_of_books_id=fnd_profile.value('GL_SET_OF_BKS_ID') order by value,description select chart_of_accounts_id from GL_LEDGERS WHERE LEDGER_ID =:$flex$.ledger US Chart of Account Chart of Account ID 4 -20 :p_value_set Number SELECT s.FLEX_VALUE_SET_ID FROM fnd_id_flex_segments s, fnd_segment_attribute_values sav, fnd_segment_attribute_types sat WHERE s.application_id = 101 AND s.id_flex_code = 'GL#' AND s.id_flex_num = :$flex$.chart_of_account AND s.enabled_flag = 'Y' AND s.application_column_name = sav.application_column_name AND sav.application_id = 101 AND sav.id_flex_code = 'GL#' AND sav.id_flex_num = :$flex$.chart_of_account AND sav.attribute_value = 'Y' AND sav.segment_attribute_type = sat.segment_attribute_type AND sat.application_id = 101 AND sat.id_flex_code = 'GL#' AND sat.unique_flag = 'Y' AND sat.segment_attribute_type = 'GL_BALANCING' US FND_NUMBER Dummy 5 10 :p_legal_entity_id LOV Oracle XLE_FIRSTPARTY Y select legal_entity_id id, name value, legal_entity_identifier description from xle_entity_profiles where transacting_entity_flag='Y' order by value,description Y US Legal Entity Legal Entity ID 6 20 :p_set_of_books_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 US Ledger Ledger Id 7 30 :p_balancing_segment LOV Oracle JE_ZZ_VAT_BALANCING_SEGMENT Y select flex_value id, flex_value value, null description from fnd_flex_values_vl where flex_value_set_id=:$flex$.fnd_number and nvl(enabled_flag,'N')='Y' and nvl(summary_flag,'N')='N' and trunc(sysdate) between trunc(nvl(start_date_active,sysdate)) and trunc(nvl(end_date_active,sysdate)) order by flex_value US Balancing Segment Balancing Segment Value 8 40 :p_period_from LOV Oracle JGZZ_SRS_AP_PERIOD_FROM Y select period_name id, period_name value, null description from gl_period_statuses where application_id=200 and closing_status!='N' and adjustment_period_flag!='Y' and set_of_books_id=fnd_profile.value('GL_SET_OF_BKS_ID') order by period_year,period_num Y US GL Period From GL Period From 9 50 :p_period_to LOV Oracle JGZZ_SRS_AP_PERIOD_TO Y select period_name id, period_name value, null description from gl_period_statuses where application_id=200 and closing_status!='N' and adjustment_period_flag!='Y' and set_of_books_id=fnd_profile.value('GL_SET_OF_BKS_ID') and start_date>=(select start_date from gl_period_statuses where period_name= :$flex$.jgzz_srs_ap_period_from and application_id=200 and set_of_books_id=fnd_profile.value('GL_SET_OF_BKS_ID')) order by period_year,period_num Y US GL Period To GL Period To 10 60 :p_vat_from LOV Oracle JGZZ_SRS_TAX_RATE Y select tax_rate_code id, tax_rate_code value, tax_rate_name description from zx_rates_vl where active_flag='Y' order by value,description US VAT Code From VAT Code From 11 70 :p_vat_to LOV Oracle JGZZ_SRS_TAX_RATE Y select tax_rate_code id, tax_rate_code value, tax_rate_name description from zx_rates_vl where active_flag='Y' order by value,description US VAT Code To VAT Code To 12 80 :p_low_range Char US Accounting Flexfield From Accounting Flexfield From 13 90 :p_high_range Char US Accounting Flexfield To Accounting Flexfield To