JE German Payables VAT Reconciliation Detail - draft

Description
Categories: BI Publisher
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 Name SQL text Validation
FND_NUMBER
 
Number
Chart of Account
 
LOV Oracle
P_coa_id
 
LOV Oracle
P_app_id
 
Number
Accounting Flexfield To
 
Accounting Flexfield From
 
VAT Code To
 
LOV Oracle
VAT Code From
 
LOV Oracle
GL Period To
 
LOV Oracle
GL Period From
 
LOV Oracle
Balancing Segment
 
LOV Oracle
Ledger
 
LOV Oracle
Legal Entity
 
LOV Oracle
Ask a question