JE German Payables VAT Reconciliation Detail Report- Not Supported: Reserved For Future Use

➜ Back to Library
Description
Categories: BI Publisher
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
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
Parameter NameSQL textValidation
Legal Entity
 
LOV Oracle
Ledger
 
LOV Oracle
Balancing Segment
 
LOV Oracle
GL Period From
 
LOV Oracle
GL Period To
 
LOV Oracle
VAT Code From
 
LOV Oracle
VAT Code To
 
LOV Oracle
Accounting Flexfield From
 
Char
Accounting Flexfield To
 
Char
Download
Related Blitz Reports
GL Account Analysis GL Account Analysis (Distributions) 11g GL Account Analysis (Distributions) EOS GL Account Analysis EOS GL Account Analysis with Tax GL Account Analysis (Drilldown) (with inventory and WIP) AP Invoice on Hold 11i JE Portuguese Payables Taxpayer ID Exception Report- Not Supported: Reserved For Future Use ZX Use Tax Liability Report- Not Supported: Reserved For Future Use
Blitz Report™